counting by letter instead of number

[ArielZusya]

I think my last thread got lost while waiting for a response so this is a
resubmission per the netiquette suggestions on mvps.org.

So... I'm working on a system that will track objects in a database. These
objects are numbered (unfortunately) as A, B, C, D, ... AA, BB, CC, DD, ...
AAA, BBB, CCC, DDD. What that ultimately means is the 27th object will
receive a designation of AA and the 28th will receive a designation of BB and
the 53rd a designation of AAA and the 54th a designation of BBB and so on.
The user of the DB enters the name of the object in a form and I'd like the
system to automatically assign the appropriate letter(s) to that object.
Dave Hargis (Klatuu) was helping me out but like I said, I believe my post
was lost in the archives... no action on it since 11/14. His suggestions so
far have been to take advantage of asc to figure out what the number
equivalent of the letter would be but that was before he learned of the
complexity (idiocy? Wish I could just tell the users they need to adopt a
more logical numbering system like AA, AB, AC, and so on) of this numbering
system.

I'm self taught in the world of VBA and so looping (which is where I imagine
this is likely to go) is a whole new world for me. Be gentle. Thanks!

Ariel

 

[John Spencer]

You might try the following.

Public Function NextInSeries(strIn As Variant)

If Len(strIn & "") = 0 Then
NextInSeries = "A"

ElseIf strIn Like "Z*" Then
NextInSeries = String(Len(strIn) + 1, "A")

Else
NextInSeries = String(Len(strIn), Chr(Asc(strIn) + 1))

End If

End Function

You might want to add a Ucase to all the assignment statements. Right now if
you pass in
"A" your will get "B" returned and if you pass in "a" you will get "b"
returned.

If you add
strIn= UCase(strIn) as the first line in the function you should always
get upper case letters returned.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

I think my last thread got lost while waiting for a response so this is a
resubmission per the netiquette suggestions on mvps.org.

So... I'm working on a system that will track objects in a database. These
objects are numbered (unfortunately) as A, B, C, D, ... AA, BB, CC, DD, ...
AAA, BBB, CCC, DDD. What that ultimately means is the 27th object will
receive a designation of AA and the 28th will receive a designation of BB and
the 53rd a designation of AAA and the 54th a designation of BBB and so on.
The user of the DB enters the name of the object in a form and I'd like the
system to automatically assign the appropriate letter(s) to that object.
Dave Hargis (Klatuu) was helping me out but like I said, I believe my post
was lost in the archives... no action on it since 11/14. His suggestions so
far have been to take advantage of asc to figure out what the number
equivalent of the letter would be but that was before he learned of the
complexity (idiocy? Wish I could just tell the users they need to adopt a
more logical numbering system like AA, AB, AC, and so on) of this numbering
system.

I'm self taught in the world of VBA and so looping (which is where I imagine
this is likely to go) is a whole new world for me. Be gentle. Thanks!

With this bizarre scheme, it's actually fairly easy.
Because all the letters in the "numbers" are all the same,
you only need to get the value of any one of them.

I think all you need to translate an ordinary number, x, to
the letters is code like:

String(1 + (x - 1) \ 26, Chr(65 + ((x - 1) Mod 26)))

and to translate one of your weird things, c, to an ordinary
number:

(Len(c) - 1) * 26 + Asc(Left(c, 1)) - 64

 

[Lance]

Give this a try

Function Letter2Number(invalue As String)
Const alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim i As Integer
invalue = UCase(invalue)
Letter2Number = 0

Letter2Number = (Len(invalue) - 1) * 26
Letter2Number = Letter2Number + InStr(alphabet, Left(invalue, 1))
End Function

Function Number2Letter(invalue As Integer)
Const alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim i As Integer
Dim letter As String

letter = Mid(alphabet, (invalue Mod 26), 1)

Number2Letter = letter

For i = 1 To Int(invalue / 26)
Number2Letter = Number2Letter & letter
Next i
End Function

 

[ArielZusya]

Hi John,

That's fantastic! That is precisely what I was looking for! thanks for
your help.


Ariel