# Thread: Sequential Numbering? (Sort of...)

1. Registered User
Join Date
Apr 2002
Location
Posts
2

## Unanswered: Sequential Numbering? (Sort of...)

As you would have it, one of my clients asked me if I could produce a sequential number field in a database of 400,000 records. For example, the code should look like "A2234", a letter and four digits. However I can't use the letters "I, L OR O" and can't use numbers "1, 7 or 0".

There are supposed to be some 800,000 combinations and each one has to be unique. I thought of using 4 different tables with the needed letters and numbers and somehow using those, but I am at a loss.

Any suggestions would be greatly appreciated.

2. Registered User
Join Date
Jun 2003
Location
Belgium
Posts
133

## Re: Sequential Numbering? (Sort of...)

To Stomper :

Believe it or not, I had about the same demand some time ago.
I looked up the code for it and adapted to fit your needs.

You need one table called "tblList with 2 fields : "Character" and "CharNr". It must contain in the first field the characters that are allowed (=alphabet minus "I, L OR O" ) and in the second field a consecutive numbering (Autonumber will do fine)

You then need to create a module with the following code :

Public Function NextSeqNr(strMaxNr As String) As String

Dim strFirstChar As String, intNr As Integer

'split the given maximum seqnumber
strFirstChar = Left(strMaxNr, 1)
intNr = Mid(strMaxNr, 2)

RaiseNr:
intNr = intNr + 1

If intNr = 10000 Then
intNr = 0
strFirstChar = RaiseChar(strFirstChar)
End If

If (InStr(1, Format(intNr, "0000"), "1") + _
InStr(1, Format(intNr, "0000"), "7") + _
InStr(1, Format(intNr, "0000"), "0")) > 0 Then GoTo RaiseNr

NextSeqNr = strFirstChar & Format(intNr, "0000")

End Function

Public Function RaiseChar(strChar As String) As String

Dim i As Integer

i = DLookup("CharNr", "tblList", "Character='" & strChar & "'")
RaiseChar = DLookup("Character", "tblList", "CharNr=" & i + 1)

End Function

When all is well, you can use the function :
=NextSeqNr(DMax("YourFieldName","YourTable"))
as entry for the default value in the field "YourFieldName" (This is the field in the table 'YourTable' where you need the sequential number

Like this, the next available sequential number will be generated autoimatically for each new record you need to add. Just make sure there is at least one value entered manually to get the routine started (otherwise, the DMax function would give a NULL value!)
Last edited by Herman; 09-03-03 at 11:19.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•