Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Madison, NJ

    Question 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. #2
    Join Date
    Jun 2003

    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)

    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 :
    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