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

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
 

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:

Public Function fIncrementLetters(strLastLetters As String)
'Check for exactly 2 Letters - (Upper or Lower Case)
If Len(strLastLetters) <> 2 Then Exit Function
If Asc(UCase$(Left$(strLastLetters, 1))) < 65 Or Asc(UCase$(Left$(strLastLetters, 1))) > 90 Then Exit Function
If Asc(UCase$(Right$(strLastLetters, 1))) < 65 Or Asc(UCase$(Right$(strLastLetters, 1))) > 90 Then Exit Function
 
If UCase$(Right$(strLastLetters, 1)) <> "Z" Then
  fIncrementLetters = UCase$(Left$(strLastLetters, 1)) & Chr$(Asc(UCase$(Right$(strLastLetters, 1))) + 1)
Else
  fIncrementLetters = Chr$(Asc(UCase$(Left$(strLastLetters, 1))) + 1) & "A"
End If
End Function

SAMPLE OUTPUT:
fIncrementLetters("lz") ==> "MA"
fIncrementLetters("AA") ==> "AB"
fIncrementLetters("Qg") ==> "QH"
fIncrementLetters("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:

Public Function fIncrementLetters(strLastLetters As String)
'Check for exactly 2 Letters - (Upper or Lower Case)
If Len(strLastLetters) <> 2 Then Exit Function
If Asc(UCase$(Left$(strLastLetters, 1))) < 65 Or Asc(UCase$(Left$(strLastLetters, 1))) > 90 Then Exit Function
If Asc(UCase$(Right$(strLastLetters, 1))) < 65 Or Asc(UCase$(Right$(strLastLetters, 1))) > 90 Then Exit Function
 
If UCase$(Right$(strLastLetters, 1)) <> "Z" Then
  fIncrementLetters = UCase$(Left$(strLastLetters, 1)) & Chr$(Asc(UCase$(Right$(strLastLetters, 1))) + 1)
Else
  fIncrementLetters = Chr$(Asc(UCase$(Left$(strLastLetters, 1))) + 1) & "A"
End If
End Function

SAMPLE OUTPUT:
fIncrementLetters("lz") ==> "MA"
fIncrementLetters("AA") ==> "AB"
fIncrementLetters("Qg") ==> "QH"
fIncrementLetters("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:

If UCase$(strLastLetters) = "ZZ" Then Exit Function

[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