AlphaNumeric AutoNumber

P

Proko

Is it possible to format an auto number to follow the sequence
A0001,A0002,......A9999,B0000,B0001......B9999,C0000 etc

I am currently using the format \A0000 but foresee a problem after A9999
when access creates a sixth character by adding another digit and goes to
A10000, A10001...etc. I require there to be only 5 characters.

Or is there a better way?

Any help would be greatly appreciated.
 
J

John W. Vinson

Is it possible to format an auto number to follow the sequence
A0001,A0002,......A9999,B0000,B0001......B9999,C0000 etc
Click to expand...

No. An Autonumber is a specially handled Long Integer; it does not and cannot
contain alpha characters.
I am currently using the format \A0000 but foresee a problem after A9999
when access creates a sixth character by adding another digit and goes to
A10000, A10001...etc. I require there to be only 5 characters.

Or is there a better way?
Click to expand...

You'll need to use a Text field (not any sort of number, no number field can
contain letters), and write VBA code to increment it. Something like (untested
air code):

Private Function NextID() As String
Dim strID As String
Dim nID As Integer
strID = DMax("ID", "YourTableName")
nID = Val(Mid(strID, 2)
If nID < 9999 Then
NextID = Left(strID, 1) & Format(nID + 1, "00000")
Else
If Left(strID, 1) = "Z" Then
MsgBox "Turn off your computer, and go home. No more ID's."
NextID = "BYEBYE"
Else
NextID = Chr(Asc(Left(strID, 1)) + 1) & "00000"
End If
End If
End Function

John W. Vinson [MVP]
 
N

NewSysAdmin

I've been trying to get your database to work for me. In the tblAlphaNum,
I've tried deleting the first record and typing in the format I want for the
RecID field. I also did compact and repair and retried it. However, it keeps
going back to your format of AA001. Am I missing something?

I know that auto-number can cause problems when you try to place
significance to it, which we want to do. Is there an easy way to set up
auto-increment with alpha-numeric characters? I am not very strong in the
VB, so would like to find a way around using it. Thank you.
 
Top