creating a sequential alpha-numeric case number (1 Viewer)

[xBirdman]

I must create a sequential case number based on the previous case in the series. I have multiple series, e.g., AA, BA, XYZ. Each series is coded by two-digit year, and sequential number. Some series have 4 digit numbers and some series have 3 digit numbers, so the first AA case for 2019 would be AA190001, while the first for the BA series would be BA19001. Add to that some series are 2 letters, and some are 3 (e.g., AA vs. XYZ) and you have a mess.
Assume I need to add a new case to the AA series, and the last one was AA190500. The user provides the series (AA) and year (2019). I concatenate as AA19 as a prefix and then use it to filter a sorted query of cases, and grab the last with a call to DMax()
Once I have the last case number, I use the length of my prefix (which depending on the series is 4 or 5 characters) to get the sequential case number.
In my example above I get 0500, but because this has to be numeric to increment, I lose the leading 0, thus when I concatenate my prefix and the new number, I’m a number short if there was a preceding 0, and my new number comes out as AA19501.
My question is, how can I make sure to retain one or more leading zeroes if they exist?
In an effort to keep this short I’ve cut out a lot of implicit steps (declaring variables, incrementing the number by one) etc. but all the steps work. I just need to be able to keep that leading 0. If further reference is needed, see below.

[SIZE=3][FONT=Calibri]   Dim pref As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim lastCase As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim L As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim csNum As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim nxtCase As String[/FONT][/SIZE]

[SIZE=3][FONT=Calibri]               ‘create prefix[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   pref = Me.cboCaseType.Column(1) & Right(Me.txtCurrentYr, 2) & "*"[/FONT][/SIZE]

[SIZE=3][FONT=Calibri]               ‘using prefix as a filter, get the most recent case number[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   lastCase = DMax("[IncidentNumber]", "qry_listCases")[/FONT][/SIZE]

[SIZE=3][FONT=Calibri]               ‘get the length of prefix (minus the asterisk wildcard)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   L = Len(pref) – 1[/FONT][/SIZE]

[SIZE=3][FONT=Calibri]               ‘pull the sequential case number and increment it[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   csNum = Right(lastCase, Len(lastCase) - L) + 1[/FONT][/SIZE]

[SIZE=3][FONT=Calibri]               ‘concatenate the prefix and incremented value[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   nxtCase = Left(pref, Len(pref) - 1) & csNum[/FONT][/SIZE]

Additional notes that may not be needed:
At the most extreme, e.g., at the beginning of a new year when you have a number like AA200002 the created value would be AA203, so I need it to be flexible enough to account for this if possible. I’ll embed the entire routine in an If() statement to handle the unusual situation when the year turns over and the first annual record (-001) needs to be added.
As further background, I’m working with a very old database with plenty of problems – bad table structure, difficulty with setting up unambiguous relationships, fuzzy data entry protocols through the years. I’m charged with making the best of the bad situation for the next 2 years as we bring online a modern up to date system that resolves most of these historic issues. I’m not able to make many substantial changes to the underlying structure, so my goal is to clean up the process for daily users via forms and under-the-hood mechanics that maintain data integrity for this short life expectancy. This was driven by legislative changes in the programs we administer and the lag-time involved in adding these programs to the new system in the later builds.

 

[CJ_London]

since you are converting it to text, you can add the preceding zero by formatting as "0000"

eg

nxtCase = Left(pref, Len(pref) - 1) & format(csNum,"0000")

 

[xBirdman]

Brilliant. I think that will work. I'll have to monkey around with the code a little to make it flexible for the various different formats but I think I can make it work. Thanks!

 

[CJ_London]

think you should be able to determine it from the length of your lastcase variable

 

[sxschech]

Here is one that I use and it preserves leading zeroes. It may need adjusting for your number scheme as ours uses a dash in the item number.

Function RegexIncrement(ByVal NumToIncrement As String)
'Increment a text string number by 1
'(add 1 to the number)
'(add one to the number)
'and keep any leading zeroes.
'To keep leading zeroes, split out the numeric from the text
'count number of characters in the number and use that in
'the format function when concatenating back with the text
'Original code did not require breaking string up, but in
'that case, it did not preserve the leading zeroes
'Post #5:
'https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/144719-increment-number-within-variable-length-alphanumeric-string
'
'https://answers.microsoft.com/en-us/msoffice/forum/all/repeat-function/6a6142bc-6fae-403c-af5d-ca471c13ced4
'20181126
    Dim x As Variant
    Dim z As Long
    Dim i As Long
    Dim stAlpha As String
    Dim stLeadZero As String
    Dim stLeadZeroFormat As String
    
    stAlpha = Left(NumToIncrement, InStrRev(NumToIncrement, "-"))
    stLeadZero = Mid(NumToIncrement, InStrRev(NumToIncrement, "-") + 1)
    stLeadZeroFormat = String(Len(Mid(NumToIncrement, InStrRev(NumToIncrement, "-") + 1)), "0")
    
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = False
        .Pattern = "[0-9]+"
            For Each x In .Execute(stLeadZero)
            RegexIncrement = Replace(stLeadZero, x, x + 1)
            Next x
    End With
    RegexIncrement = stAlpha & Format(RegexIncrement, stLeadZeroFormat)
End Function

 

[xBirdman]

Thanks sxschech for the response and code. It looks very similar to what I ultimately cobbled together, though yours appears to be a bit more... elegant? I am just learning vb so don't even know a lot of the calls you include.

Basically though, and I literally just finished working this out, I split the original string, got a length of prefix and suffix portions, converted the suffix to a number and incremented it, then converted back to string and concatenated back with the prefix. Probably very inelegant but it seems to work, handles different length series codes and suffixes, and loads the form for the new case with all the necessary fields updated. You guys rock and the assist is always appreciated.