AlphaNumeric AutoNumber

[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.

 

[Dennis]

I think Autonumber is for Long Integers only.

 

[John W. Vinson]

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

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?

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]

 

[Ron2006]

lol

 

[Arvin Meyer [MVP]]

Here's something that will work from AA001 through ZZ999

http://www.datastrat.com/Download/AlphaNumeric2K.zip

You can probably alter it to what you want.

 

[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.