If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Next in alphabet
Hi. I would like to create a loop to update some fields in a table. What
I'd like is update one field in a table based on another. I want it to happen like this: the second letter in my string field ("YCode") goes up by one letter as my double numeric field ("YMAX") to steps down by 10 at each increment. So if YMAX is 2343640, say YCode will be GK. The next in the sequence would therefore be YMAX=2346330 YCode=GL the next YMAX=2346320 YCode=GM What I've got now is a clunker, that only updates about 1000 records at a time, and I've got over 400,000 records to update: Function YC(Ycoord As Double, GridCode As String) As String Dim db As DAO.Database Dim strSQL As String Set db = CurrentDb strSQL = "UPDATE Grid10m SET Grid10m.YCode = '" & GridCode & "' WHERE (((Grid10m.YMAX)=" & Ycoord & "));" db.Execute strSQL, dbFailOnError Debug.Print "Completed " & GridCode & ", " & Ycoord End Function Can anyone tell me how to have the VB go to the next letter in the alphabet with the next increment? AK |
#2
|
|||
|
|||
Next in alphabet
Make a table with two fields: one for the letter and another for number,
1-26, like this: (Let's call the table AlphaOrder) Letter Position A 1 B 2 C 3 etc. Z 26 Now you can make any kind of loop you want, incrementing forward or backward on the Position field (e.g. for X = 1 to 26, etc.) and retrieving the associated letter. For example, you can make a loop like this (warning: air code - I have not tested it, but it should get you close) Dim intOrder as Integer Dim strNext as String For intOrder = 26 to 1 step -1 strNext = DLookup("[Letter]","[AlhpaOrder]","[Position] = " & intOrder) 'do stuff with it here Next intOrder This would generate each letter in succession, and you may want, instead, to look up just the next one based on a known YCode entry. In this case, you need to first identify the last character, find its ordinal postion, subtract 1, and identify that position's letter, like this: Dim strLetter as String Dim intPosition as Integer strLetter = right(YCode,1) 'gets the last letter intPosition = DLookup("[Position]","[AlhpaOrder]","[Letter] = '" & strLetter & "'") intPosition = intPosition - 1 If intPosition 0 Then strLetter = DLookup("[Letter]","[AlhpaOrder]","[Position] = " & intPosition ) 'strLetter is now the letter before the last character of YCode five lines above here and can be used End If "accesskastle" wrote: Hi. I would like to create a loop to update some fields in a table. What I'd like is update one field in a table based on another. I want it to happen like this: the second letter in my string field ("YCode") goes up by one letter as my double numeric field ("YMAX") to steps down by 10 at each increment. So if YMAX is 2343640, say YCode will be GK. The next in the sequence would therefore be YMAX=2346330 YCode=GL the next YMAX=2346320 YCode=GM What I've got now is a clunker, that only updates about 1000 records at a time, and I've got over 400,000 records to update: Function YC(Ycoord As Double, GridCode As String) As String Dim db As DAO.Database Dim strSQL As String Set db = CurrentDb strSQL = "UPDATE Grid10m SET Grid10m.YCode = '" & GridCode & "' WHERE (((Grid10m.YMAX)=" & Ycoord & "));" db.Execute strSQL, dbFailOnError Debug.Print "Completed " & GridCode & ", " & Ycoord End Function Can anyone tell me how to have the VB go to the next letter in the alphabet with the next increment? AK |
#3
|
|||
|
|||
Next in alphabet
It might be simpler to know that A is Ascii 65, Z is Ascii 90, and all the
letters are between those two values. No table is required that way... -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Brian" wrote in message ... Make a table with two fields: one for the letter and another for number, 1-26, like this: (Let's call the table AlphaOrder) Letter Position A 1 B 2 C 3 etc. Z 26 Now you can make any kind of loop you want, incrementing forward or backward on the Position field (e.g. for X = 1 to 26, etc.) and retrieving the associated letter. For example, you can make a loop like this (warning: air code - I have not tested it, but it should get you close) Dim intOrder as Integer Dim strNext as String For intOrder = 26 to 1 step -1 strNext = DLookup("[Letter]","[AlhpaOrder]","[Position] = " & intOrder) 'do stuff with it here Next intOrder This would generate each letter in succession, and you may want, instead, to look up just the next one based on a known YCode entry. In this case, you need to first identify the last character, find its ordinal postion, subtract 1, and identify that position's letter, like this: Dim strLetter as String Dim intPosition as Integer strLetter = right(YCode,1) 'gets the last letter intPosition = DLookup("[Position]","[AlhpaOrder]","[Letter] = '" & strLetter & "'") intPosition = intPosition - 1 If intPosition 0 Then strLetter = DLookup("[Letter]","[AlhpaOrder]","[Position] = " & intPosition ) 'strLetter is now the letter before the last character of YCode five lines above here and can be used End If "accesskastle" wrote: Hi. I would like to create a loop to update some fields in a table. What I'd like is update one field in a table based on another. I want it to happen like this: the second letter in my string field ("YCode") goes up by one letter as my double numeric field ("YMAX") to steps down by 10 at each increment. So if YMAX is 2343640, say YCode will be GK. The next in the sequence would therefore be YMAX=2346330 YCode=GL the next YMAX=2346320 YCode=GM What I've got now is a clunker, that only updates about 1000 records at a time, and I've got over 400,000 records to update: Function YC(Ycoord As Double, GridCode As String) As String Dim db As DAO.Database Dim strSQL As String Set db = CurrentDb strSQL = "UPDATE Grid10m SET Grid10m.YCode = '" & GridCode & "' WHERE (((Grid10m.YMAX)=" & Ycoord & "));" db.Execute strSQL, dbFailOnError Debug.Print "Completed " & GridCode & ", " & Ycoord End Function Can anyone tell me how to have the VB go to the next letter in the alphabet with the next increment? AK |
#4
|
|||
|
|||
Next in alphabet
Thanks Brian! That's a great idea to use a table with a field for letter and
another for position. I'll try it and let you know how it worked out. AK "Brian" wrote: Make a table with two fields: one for the letter and another for number, 1-26, like this: (Let's call the table AlphaOrder) Letter Position A 1 B 2 C 3 etc. Z 26 Now you can make any kind of loop you want, incrementing forward or backward on the Position field (e.g. for X = 1 to 26, etc.) and retrieving the associated letter. For example, you can make a loop like this (warning: air code - I have not tested it, but it should get you close) Dim intOrder as Integer Dim strNext as String For intOrder = 26 to 1 step -1 strNext = DLookup("[Letter]","[AlhpaOrder]","[Position] = " & intOrder) 'do stuff with it here Next intOrder This would generate each letter in succession, and you may want, instead, to look up just the next one based on a known YCode entry. In this case, you need to first identify the last character, find its ordinal postion, subtract 1, and identify that position's letter, like this: Dim strLetter as String Dim intPosition as Integer strLetter = right(YCode,1) 'gets the last letter intPosition = DLookup("[Position]","[AlhpaOrder]","[Letter] = '" & strLetter & "'") intPosition = intPosition - 1 If intPosition 0 Then strLetter = DLookup("[Letter]","[AlhpaOrder]","[Position] = " & intPosition ) 'strLetter is now the letter before the last character of YCode five lines above here and can be used End If "accesskastle" wrote: Hi. I would like to create a loop to update some fields in a table. What I'd like is update one field in a table based on another. I want it to happen like this: the second letter in my string field ("YCode") goes up by one letter as my double numeric field ("YMAX") to steps down by 10 at each increment. So if YMAX is 2343640, say YCode will be GK. The next in the sequence would therefore be YMAX=2346330 YCode=GL the next YMAX=2346320 YCode=GM What I've got now is a clunker, that only updates about 1000 records at a time, and I've got over 400,000 records to update: Function YC(Ycoord As Double, GridCode As String) As String Dim db As DAO.Database Dim strSQL As String Set db = CurrentDb strSQL = "UPDATE Grid10m SET Grid10m.YCode = '" & GridCode & "' WHERE (((Grid10m.YMAX)=" & Ycoord & "));" db.Execute strSQL, dbFailOnError Debug.Print "Completed " & GridCode & ", " & Ycoord End Function Can anyone tell me how to have the VB go to the next letter in the alphabet with the next increment? AK |
#5
|
|||
|
|||
Next in alphabet
Thanks, it worked, and the advice about the Char function was helpful too. I
have to admit I still cheated: created an excel spreadsheet based off of char() and row(), imported it to Access and then pretty much modified the code I had below to loop through the imported spreadsheet and update the table I wanted based on a where clause. AK "Douglas J. Steele" wrote: It might be simpler to know that A is Ascii 65, Z is Ascii 90, and all the letters are between those two values. No table is required that way... -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Brian" wrote in message ... Make a table with two fields: one for the letter and another for number, 1-26, like this: (Let's call the table AlphaOrder) Letter Position A 1 B 2 C 3 etc. Z 26 Now you can make any kind of loop you want, incrementing forward or backward on the Position field (e.g. for X = 1 to 26, etc.) and retrieving the associated letter. For example, you can make a loop like this (warning: air code - I have not tested it, but it should get you close) Dim intOrder as Integer Dim strNext as String For intOrder = 26 to 1 step -1 strNext = DLookup("[Letter]","[AlhpaOrder]","[Position] = " & intOrder) 'do stuff with it here Next intOrder This would generate each letter in succession, and you may want, instead, to look up just the next one based on a known YCode entry. In this case, you need to first identify the last character, find its ordinal postion, subtract 1, and identify that position's letter, like this: Dim strLetter as String Dim intPosition as Integer strLetter = right(YCode,1) 'gets the last letter intPosition = DLookup("[Position]","[AlhpaOrder]","[Letter] = '" & strLetter & "'") intPosition = intPosition - 1 If intPosition 0 Then strLetter = DLookup("[Letter]","[AlhpaOrder]","[Position] = " & intPosition ) 'strLetter is now the letter before the last character of YCode five lines above here and can be used End If "accesskastle" wrote: Hi. I would like to create a loop to update some fields in a table. What I'd like is update one field in a table based on another. I want it to happen like this: the second letter in my string field ("YCode") goes up by one letter as my double numeric field ("YMAX") to steps down by 10 at each increment. So if YMAX is 2343640, say YCode will be GK. The next in the sequence would therefore be YMAX=2346330 YCode=GL the next YMAX=2346320 YCode=GM What I've got now is a clunker, that only updates about 1000 records at a time, and I've got over 400,000 records to update: Function YC(Ycoord As Double, GridCode As String) As String Dim db As DAO.Database Dim strSQL As String Set db = CurrentDb strSQL = "UPDATE Grid10m SET Grid10m.YCode = '" & GridCode & "' WHERE (((Grid10m.YMAX)=" & Ycoord & "));" db.Execute strSQL, dbFailOnError Debug.Print "Completed " & GridCode & ", " & Ycoord End Function Can anyone tell me how to have the VB go to the next letter in the alphabet with the next increment? AK . |
Thread Tools | |
Display Modes | |
|
|