Next Available Number that includes missing gap numbers

[JMongi]

I'm working on a little app to assign and track our drawing numbers. As such, our current drawings are going to have some gaps in the drawing number scheme.
I can use a Dmax to find the greatest number and increment that. But, that won't do much for filing in gaps of missing numbers. I'm doing reading on various numeric functions in VBA but thought I would throw this out to you VBA experts!

As a simplified clarifying example, I might have the root drawing numbers of:

00001
00002
00003
00010
00015

Using DMax plus increment would start me at 00016 and the app would never fill in 00004, etc.

 

[gemma-the-husky]

I would ignore the old gaps, and carry on from the new number.

Rather than use a dmax, I would store the next number in a table somewhere, so you can start a new sequence if you ever have the need.

If you want to fill the gaps, either enter some dummy records or renumber the records, but that is likely to affect other tables in the database.

 

[plog]

1. Why? What hardship does a gap create?

2. "00003" doesn't look like a number--numbers don't have leading zeroes. Are you storing it as a number or text?

3A. Method 1 is to load all the numbers into a recordset ordered numerically, iterate through the whole thing and make sure each number is 1 more than the prior one.

3B. Method2 is to create a table of of numbers that will exceed the highest possible ID, then create a query from that table to your ID table, show all from the Number table and then show records where the ID value is NULL.

 

[JMongi]

Just to clarify, these are not table ID numbers at all. These are unique drawing numbers. We have gaps because we've never really had a great system for managing drawings. I don't even know how large the gaps will be. It's certainly easier to ignore them but I'm investigating NOT ignoring them.

@gemna-the-husky - Why avoid DMax?

@plog - Technically these would be strings because of the need for leading zeros. An Example Drawing number would be "FD-00056-01"

 

[sxschech]

Are you incrementing like this?
"FD-00056-01"
"FD-00056-02"
"FD-00056-03"

If so, this function should help.

Function RegexIncrement(ByVal NumToIncrement As String, Optional sepchar 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
'Added optional sepchar so that can keep leading zeroes
'since now also using this function on numbers that contain
'space rather than dash between text and number with a
'leading zero
'20200115
    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, sepchar))  '"-"))
    stLeadZero = Mid(NumToIncrement, InStrRev(NumToIncrement, sepchar) + 1) '"-") + 1)
    stLeadZeroFormat = String(Len(Mid(NumToIncrement, InStrRev(NumToIncrement, sepchar) + 1)), "0") '"-") + 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

You could then use a recordset and edit/append the incremented data to fill in the gaps.

 

[JMongi]

No, I just wanted to give an example of the full drawing number and that it is technically a string not a number.

The part that is incremented is the 5 digit middle part.

 

[sxschech]

I imagine you can modify the code to fit your numbering scheme by passing in up to the portion that needs incrementing and then concatenate it back together once incremented

 

[Cotswold]

Why not just add all drawing numbers? Presumably the blanks are drawing numbers that haven't been used. So just filter out any drawings without a title, revision or issued flag. When a drawing number is used, just add a title or an issued flag and it will appear as you want it to.

 

[Pat Hartman]

I would not attempt to fill the gaps.

 

[Pat Hartman]

Also, I wouldn't use the drawing number as the PK. When you use "smart" codes as the PK that tends to come back to haunt you. Use an autonumber as the PK and therefore the FK to all other tables.

 

[JMongi]

@Pat Hartman - Definitely true. I think an assumption got made somewhere in the thread that I was talking about PKs but I am not.
@Cotswold - That is an interesting suggestion, I never thought of creating 99,999 records in advance. Interesting concept.
@sxschech - Thanks for the code reference!

It seems the consensus is that trying to fill in any existing gaps programmatically is not worth the trouble.

 

[Pat Hartman]

I would NOT advise adding "blank" records. That just gives them other ways to make errors. Plus you don't actually know in advance how many drawings you need for a given project.

 

[JMongi]

@Pat Hartman - I wasn't planning on it, but it was a novel idea.

I don't like to implement code I don't personally understand so I wrote my own functions for incrementing a string full of numbers including leading zeroes.

Function LeadingZeroIncrement(ByVal NumToIncrement As String) As String
'This function takes a number with leading zeroes in string format and increments it by 1
Dim intNumDigits, intDwgNum, intStringLength As Integer
Dim strLeadingZero, strAfterZero As String

'Determine the length of the inputted string, the numeric value and it's string length
intStringLength = Len(NumToIncrement)
intDwgNum = Val(NumToIncrement)
intNumDigits = Len(intDwgNum)

'Use the preceding information to separate the leading zeros, increment the number and concatenate
strLeadingZero = Left(NumToIncrement, intStringLength - intNumDigits)
strAfterZero = Right(NumToIncrement, intNumDigits)
intDwgNum = intDwgNum + 1
LeadingZeroIncrement = strLeadingZero & intDwgNum

 

[Pat Hartman]

I wasn't planning on it, but it was a novel idea.

Good.

You should be keeping the sequence number as a NUMBER not as a string. That way you can use simple arithmetic to increment the value.

Here's an example that shows how to build a "friendly" ID and how to build a sequence number for details.

 

[JMongi]

Looks like I'm doing very similarly to this form, but I was unaware of Format(Me.txtSeqNum, "000")

 

[Pat Hartman]

The number of zeros controls the length of the fixed numeric portion. So "00000" makes strings of 5 characters in length. "000" makes three character strings. Use the number of zeros that will provide enough room for what you want to accommodate but which is not so long that you have lots of extra zeros.

 

[theDBguy]

Looks like I'm doing very similarly to this form, but I was unaware of Format(Me.txtSeqNum, "000")

Hi. I also like using the Format() function to provide leading zeroes. However, please understand that it doesn't necessarily limit the size of your string. For example, if you use Format(99999, "000"), you won't get 999 back; instead, you will still get 99999.

 

[arnelgp]

you don't need to fill the gap?
go to Engineering and ask for their Drawing number list (in excel format)
and import them.

 

[The_Doc_Man]

You've gotten some other feedback on this, but here is the way I see it.

Whoever dealt with these drawing numbers before you didn't care enough to address the gaps. They left the gaps unchanged long enough for the problem to fall into your lap and nobody cared. Why should you allow yourself to lose even a half a second of sleep over it?

 

[MarkK]

Gaps in a series assumes there is a definition of the series without gaps. To find gaps, iterate through the pristine series, and compare each element in the series you are testing. The first time they mismatch (assuming there are no duplicates in the test series) there is one missing. Here's a simple example...

Function GetFirstMissingLong(Table As String, Field As String) As Long
    Dim sql As String
    Dim i As Long
   
    ' construct SQL
    sql = Join(Array("SELECT", Field, "FROM", Table, "ORDER BY", Field))
   
    With CurrentDb.OpenRecordset(sql)       ' open recordset of series to test
        For i = 1 To DMax(Field, Table)     ' iterate thru pristine series
            If i <> .Fields(Field) Then     ' if there is no match
                GetFirstMissingLong = i     ' then we have our missing number
                Exit For
            End If
            .MoveNext
        Next
    End With
End Function