Advancing Numbers and Letters

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I have a field that contains a number, whenever I add another record I
want the number to go to 1A then 1B, 1C...etc. Them 1AA, 1AB etc,
Anyone know how to do this?
Thanks
DS
 
I have a field that contains a number, whenever I add another record I want
the number to go to 1A then 1B, 1C...etc. Them 1AA, 1AB etc, Anyone know
how to do this?
Click to expand...

What comes after 1ZZ ?
 
See if this floats your boat

Public Function NextID(strLastID As String) As String
Dim a As String

a = Right(strLastID, 1)
If a = "Z" Or IsNumeric(a) Then
NextID = UCase(strLastID & "A")
Else
NextID = UCase(Left(strLastID, Len(strLastID) - 1) & Chr(Asc(a) +
1))
End If
End Function

Put this function in a module and call it from where ever you need the next
ID

NewID = NextID(CurrentMaxID)
 
Back
Top