Looping through alphabet

[David A. Hersher]

I am doing an application that will assign an order number to orders
entered. The number will be five digits and I have that part done. What I
want to do is assign an alphabet letter to each order number as a suffix
(i.e. 00001 A). When the order reaches 99999 A I then want it to be 00001 B
and on and on. I know it involves some type of loop but I'm not sure how to
do it. Any help would be appreciated.

 

[Luiz Cláudio]

I don't know about your db design, but one possible approach is to work with
Asc numbers, since it's easier to loop through and to return the next one:

Asc("A") = 65
Asc("B") = 66
....
Asc("Z") = 90

To show the letter, you could use:
Chr(65) = "A"
Chr(66) = "B"
....
Chr(90) = "Z"

Eg:

YourOrder: =Format$([OrderNum], "00000") & Chr([OrderLetterAsc])

If you create a query ordered by the letter (Asc number) and then by the
order number (five-digit), it will be easy to manage it.

Anyway, there are "n" possible solutions.


Luiz Cláudio C. V. Rocha
São Paulo - Brazil