Increment letters VBA (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:50
Joined
May 7, 2009
Messages
19,169
you are right, then modify the function, with or without pk this should work.
Code:
Public Function NextSeries() As String
    Dim julian As String
    Dim c As Variant
    julian = Format(DatePart("y", Date), "000")
    c = DMax("Len(autoGen) & autoGen", "theTable", "Left(autoGen,3)='" & julian & "'") & ""
    If c = "" Then
        c = "a"
    Else
        c = Mid(c, InStr(c, julian))
        c = Dec2Alpha(Alpha2Dec(Replace(c, julian, "")) + 1)
    End If
    NextSeries = julian & c
End Function
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:50
Joined
Jan 20, 2009
Messages
12,849
This will only work if the table has a primary key like autonumber, one that is always ascending. theAutoGenField cannot be the primary key of the table.

Best not rely on autonumber to be anything that matters. Numbers in the sequence can be skipped under some circumstances.
 

Users who are viewing this thread

Top Bottom