Incrementing Letters

[Lilbrat]

Im not finding anything similar in the Posts so correct me if Im wrong...

Im real new to programing in Access (Been a while since I programed in anything). The question I have is.. How do you make letters increment?

It may sound weird but, I need to create unique numbers based on day and year. That part I can do. The last two Digits in the Number are two letters though ... AA through ZZ and I cant figure out how to get those to increment through all their values. i.e. AA,AB,AC,AD.... ZY,ZZ.

Any ideas how to do this?

[Jim Doherty]

Im not finding anything similar in the Posts so correct me if Im wrong...

Im real new to programing in Access (Been a while since I programed in anything). The question I have is.. How do you make letters increment?

It may sound weird but, I need to create unique numbers based on day and year. That part I can do. The last two Digits in the Number are two letters though ... AA through ZZ and I cant figure out how to get those to increment through all their values. i.e. AA,AB,AC,AD.... ZY,ZZ.

Any ideas how to do this?

Unless someone else grasps this I cannot see in what context you are wanting to use it yes I understand the concept of AA then AB then AC for 26 repetitions on the A ........then onto B for 26 repetitions till we exhaust that then onto C and so on but in what real practical sense you wish to use it then.... No

If you merely want to have a viewable list of the combinations to work with then create a ONE field table add the letters of the alphabet to it and add this table to a query TWICE. do not join anything between the two tables simply run the query you will have a list of every possible combination. This is known in SQL parlance as a CROSS join every combination of everything so to speak

I'm not convinced I've helped you

Regards

Jim

[JConsulting]

Im not finding anything similar in the Posts so correct me if Im wrong...

Im real new to programing in Access (Been a while since I programed in anything). The question I have is.. How do you make letters increment?

It may sound weird but, I need to create unique numbers based on day and year. That part I can do. The last two Digits in the Number are two letters though ... AA through ZZ and I cant figure out how to get those to increment through all their values. i.e. AA,AB,AC,AD.... ZY,ZZ.

Any ideas how to do this?

This will probably work...but I'm curious what happens when you run out of alphabet

[code=vb]
Function IncrementAlpha( strIn As String) As String
'Pass this function your two letter string
'Example myNewString = IncrementAlpha( "ab")
'will return "ac"

Dim sAlphaBet As String
Dim sFirstLetter As String
Dim sSecondLetter As String
Dim X As Variant
Dim I As Long
sFirstLetter = Left(strIn, 1)
sSecondLetter = Right(strIn, 1)
sAlphaBet = "a,b,c,d,e,f,g, h,i,j,k,l,m,n,o ,p,q,r,s,t,u,v, w,x,y,z"
X = Split(sAlphaBet , ",")

If sSecondLetter = "z" Then
'loop to get next first letter
For I = I To UBound(X) - 1
If X(I) = sFirstLetter Then
sFirstLetter = X(I + 1)
sSecondLetter = "a"
Exit For
End If
Next I
Else
'loop to get the next second letter
For I = 0 To UBound(X) - 1
If X(I) = sSecondLetter Then
sSecondLetter = X(I + 1)
sFirstLetter = sFirstLetter
Exit For
End If
Next I
End If
IncrementAlpha = sFirstLetter & sSecondLetter
End Function
[/code]
J

[ADezii]

Im not finding anything similar in the Posts so correct me if Im wrong...

Im real new to programing in Access (Been a while since I programed in anything). The question I have is.. How do you make letters increment?

It may sound weird but, I need to create unique numbers based on day and year. That part I can do. The last two Digits in the Number are two letters though ... AA through ZZ and I cant figure out how to get those to increment through all their values. i.e. AA,AB,AC,AD.... ZY,ZZ.

Any ideas how to do this?

Pass this Function 2-Letters and it will do the incrementing for you:
[CODE=vb]Public Function fIncrementLette rs(strLastLette rs As String)
'Check for exactly 2 Letters - (Upper or Lower Case)
If Len(strLastLett ers) <> 2 Then Exit Function
If Asc(UCase$(Left $(strLastLetter s, 1))) < 65 Or Asc(UCase$(Left $(strLastLetter s, 1))) > 90 Then Exit Function
If Asc(UCase$(Righ t$(strLastLette rs, 1))) < 65 Or Asc(UCase$(Righ t$(strLastLette rs, 1))) > 90 Then Exit Function

If UCase$(Right$(s trLastLetters, 1)) <> "Z" Then
fIncrementLette rs = UCase$(Left$(st rLastLetters, 1)) & Chr$(Asc(UCase$ (Right$(strLast Letters, 1))) + 1)
Else
fIncrementLette rs = Chr$(Asc(UCase$ (Left$(strLastL etters, 1))) + 1) & "A"
End If
End Function[/CODE]

SAMPLE OUTPUT:
fIncrementLette rs("lz") ==> "MA"
fIncrementLette rs("AA") ==> "AB"
fIncrementLette rs("Qg") ==> "QH"
fIncrementLette rs("ZZ") ==> "[A"
...etc.

NOTE: It does not check for the last possible Letter Combination, namely ZZ as clearly indicated in the last Sample Output. I'll leave this up to you but if you are stuck, please let me know.

[JConsulting]

Pass this Function 2-Letters and it will do the incrementing for you:
[CODE=vb]Public Function fIncrementLette rs(strLastLette rs As String)
'Check for exactly 2 Letters - (Upper or Lower Case)
If Len(strLastLett ers) <> 2 Then Exit Function
If Asc(UCase$(Left $(strLastLetter s, 1))) < 65 Or Asc(UCase$(Left $(strLastLetter s, 1))) > 90 Then Exit Function
If Asc(UCase$(Righ t$(strLastLette rs, 1))) < 65 Or Asc(UCase$(Righ t$(strLastLette rs, 1))) > 90 Then Exit Function

If UCase$(Right$(s trLastLetters, 1)) <> "Z" Then
fIncrementLette rs = UCase$(Left$(st rLastLetters, 1)) & Chr$(Asc(UCase$ (Right$(strLast Letters, 1))) + 1)
Else
fIncrementLette rs = Chr$(Asc(UCase$ (Left$(strLastL etters, 1))) + 1) & "A"
End If
End Function[/CODE]

SAMPLE OUTPUT:
fIncrementLette rs("lz") ==> "MA"
fIncrementLette rs("AA") ==> "AB"
fIncrementLette rs("Qg") ==> "QH"
fIncrementLette rs("ZZ") ==> "[A"
...etc.

NOTE: It does not check for the last possible Letter Combination, namely ZZ as clearly indicated in the last Sample Output. I'll leave this up to you but if you are stuck, please let me know.

Mine is prettier Dez :o)

[Lilbrat]

Thanks to both of you for your help. I'll try them both and see which one works better :)

As for checking for the last possible combination "ZZ", If I ever get 676 items in in one day....My job has gotten way too complex and I won't get them all checked in anyway, then it will be the next day and the counter starts over. :) So I don't think ZZ will ever get used

[ADezii]

Mine is prettier Dez :o)

Never said my code was pretty, only functional. (LOL).

[Lilbrat]

They both work... So Thanks to all... When it is ZZ and tries to increment it gets an error... But again no big deal cause thats way more than Ill ever need :)

[ADezii]

They both work... So Thanks to all... When it is ZZ and tries to increment it gets an error... But again no big deal cause thats way more than Ill ever need :)

No provision was made for this contingency, as indicated by the notation on Post #4. You can simpy exit the Function if this condition exists via:
[CODE=vb]If UCase$(strLastL etters) = "ZZ" Then Exit Function[/CODE]

[FishVal]

Hi, everyone.

Just another approach.
The value may be stored in table as number and converted to base 26 numeration system (digits A-Z) number when needed.

Public Function GetAZNumber(ByVal intInput As Integer) As String

    GetAZNumber = Chr(Int(intInput / 26) + Asc("A")) & _
        Chr(intInput Mod 26 + Asc("A"))

End Function