Next in alphabet

  • Thread starter Thread starter accesskastle
  • Start date Start date
A

accesskastle

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
 
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
 
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

(no private e-mails, please)


Brian said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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

(no private e-mails, please)


Brian said:
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 said:
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
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top