Item Numbering Within a Group

[NeoPa]

Overview

People often ask how to reset their indices by year. That is to say, for instance, that they want a unique index for their item but the numbering sequence should reset for each year. This is just one example of numbering within a group. In this case the grouping factor is the year. In other cases it might be that the related Product Group may have a code (Abbreviation) that is used as part of the index for the product itself.

Outside of that a sequential number is required that is appended to the group ID, sometimes after a separator character like "_" or "-".


Table Layout (for example)

[tblProduct]
 ID        Text(10)      'Laid out as "{Year}_nnnnn" where nnnnn is a sequential number within the year

Method

The approach to determine what the next index value should be would be to find the greatest value currently, split it up into its component parts, add one to the numeric sequence value, then reformulate the new value. We can do that easily in our heads but we need to get the code to do that for us reliably.

Assuming the table outlined above, not complicated as tables go, the following function procedure will return the next value from the table.

Public Function GetNextID() As String
    Dim lngSeq As Long
    Dim strWork As String
 
    strWork = Replace("([ID] Like '%Y_*')", "%Y", Format(Date(), "yyyy")
    lngSeq = Val(Right(Nz(DMax(Expr:="[ID]", _
                             , Domain:="[tblProduct]" _
                             , Criteria:=strWork), String("0", 10)), 5)) + 1
    strWork = Split(strWork, "'")(1)
    GetNextID = Replace(strWork, "*", Format(lngSeq, "00000"))
End Function

Explanation

1. Line #5 prepares a string which we'll use first as a filter, and then again later for setting up the return value.
2. Lines #6 through #8 comprise a single, continued, line of VBA.
   • DMax() finds the last, if any, value of ID already used for that year.
   • Nz() handles nothing being found (Null value from DMax()) and replaces that with a string of ten zeroes.
   • Right() returns the rightmost five characters - IE. the sequence number part of the ID.
   • Val() converts that result back into numeric format from string.
     Notice the resultant value also has 1 added to it at the end.
3. Line #9 extracts the actual pattern we used earlier from strWork.
4. Line #10 sets the return value of the function by replacing the "*" from the pattern we matched earlier with the new sequence number.

The resultant value is what is required as the next ID value in the sequence.

This example uses the Date() function, formatted as a year, to illustrate the concept. As explained earlier, anything that you want can be used in its place. EG. If you had a FiscalYear() function, that returned a numeric value of the fiscal year, then the code would be as below :

Public Function GetNextID() As String
    Dim lngSeq As Long
    Dim strWork As String
 
    strWork = Replace("([ID] Like '%Y_*')" _
                    , "%Y", Format(FiscalYear(Date()), "0000")
    lngSeq = Val(Right(Nz(DMax(Expr:="[ID]", _
                             , Domain:="[tblProduct]" _
                             , Criteria:=strWork), String("0", 10)), 5)) + 1
    strWork = Split(strWork, "'")(1)
    GetNextID = Replace(strWork, "*", Format(lngSeq, "00000"))
End Function

Notice the only differences here are in line #5 where Date() is changed to FiscalYear(Date()) and the format string has been changed from "yyyy" to "0000" as we're now dealing with a simple numeric value rather than a date. The result is still a number displayed as a four-digit string. Also, due to the line length, I've broken it down into multiple lines, but that doesn't affect the logic in any way.