Auto Numbering

[Kundan]

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]

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]

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]

If the prefix is always the same why have it in the number?, why not concatenate it with the number.

 

[GinaWhipp]

Hmm, maybe this will help...
https://regina-whipp.com/blog/?p=704

 

[Zedster]

I do this sort of thing quite a lot. I created a function to do this.

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

 

[Pat Hartman]

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?

 

[usama_hawas]

use vba and create function
then call it