Auto Numbering (1 Viewer)

Kundan

Registered User.
Local time
Today, 15:53
Joined
Mar 23, 2019
Messages
118
Can I make Access produce auto numbers of the following type:

I-17088
I-17089
I-17090
I-17091
I-17092
I-17093
I-17095
I-17096
I-17097
I-17098
I-17099

The prefix is I- and the number should have 5 digits.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
19,169
you need autonumber field.

on query:

this will start from I-0001:

Select autonumberfield, "I-" & Format(DCount("1","yourTable","autonumberField<=" & [autonumberfield]), "00000") As sequence From yourTable;

this will start from I-17088:

Select autonumberfield, "I-" & Format(DCount("1","yourTable","autonumberField<=" & [autonumberfield]) + 17087, "00000") As sequence From yourTable;
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,186
In the table design, set the format for the autonumber field to something like "I-17"000.
The numbers stored will be displayed as I-17001,I-17002 etc though will actually be 1,2...

Bear in mind that once you reach I-17999 the next record will be shown as I-171000 not I-18000
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:53
Joined
Sep 21, 2011
Messages
14,048
If the prefix is always the same why have it in the number?, why not concatenate it with the number.
 

Zedster

Registered User.
Local time
Today, 22:53
Joined
Jul 2, 2019
Messages
168
I do this sort of thing quite a lot. I created a function to do this.

Code:
Public Function GetNextIndex(strPrefix As String, strTargetTable As String, strFieldName As String) As String


    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim strNumber As String
    Dim strNewNumber As String
    Dim lngMaxNumber As Long
    Dim intPrefixLength As Integer
        
    strSQL = "SELECT " & strFieldName & " FROM " & strTargetTable & " ORDER BY " & strFieldName
    Debug.Print strSQL
    intPrefixLength = Len(strPrefix)
    lngMaxNumber = 0
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    With rs
        .MoveFirst
        Do While Not .EOF
             If Left(.Fields(strFieldName), intPrefixLength) = strPrefix Then
                strNumber = .Fields(strFieldName)
                strNumber = Right(strNumber, Len(strNumber) - intPrefixLength)
                If lngMaxNumber < CLng(strNumber) Then
                    lngMaxNumber = CLng(strNumber)
                End If
             End If
            .MoveNext
        Loop
    End With
    
    lngMaxNumber = lngMaxNumber + 1
    strNewNumber = CStr(lngMaxNumber)
    
    'Now add extra "0" to front
    
    Do While Len(strNewNumber) < Len(strNumber)
        strNewNumber = "0" & strNewNumber
    Loop

    'now add prefix back
    strNewNumber = strPrefix & strNewNumber
    GetNextIndex = strNewNumber

End Function
 
Last edited by a moderator:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:53
Joined
Feb 19, 2002
Messages
42,976
I would not use a format to concatenate the "I-". That will only confuse people. They will never be clear as to when to enter I-11111 or 11111

If I were to use a prefix, I would keep it in a separate field and print the two controls out close enough so that they look like 1 value.

What is the point of the prefix if it never changes?
 

Users who are viewing this thread

Top Bottom