Incrementing a Number in Tiers 2

[simon1974]

I have a table and a query that lists bill numbers and adjustments. I want to create a field/formula similar to an auto number where the first instance of a bill number will be assigned 000 and then increment up 001, 002, 003 until the next bill number is reached at which point it will restart at 000. I have a date field which will define the order of bills within each bill number. Any help would be much appreciated.

 

[Databaseguy]

This returns a 6 digit string. You have to pass a value and the recordset into it.

Public Sub AddBoxes(rstCount As ADODB.Recordset, lastbox As String)

With rstCount
Dim i As Integer
Dim strLength
Dim strRemainder

strLength = Len(Trim(lastbox))
strRemainder = 6 - strLength

For i = 1 To strRemainder
lastbox = "0" & lastbox
'MsgBox lastbox
Next i

.AddNew
.Fields("Box&quot = lastbox
.Update
End With

You could modify it to reset it if the value is a certain value. Or, change it into a function that returns the value.

 

[PauloRico]

Create a new Module and paste in the following code -

Global gvarBillNumber as Variant
Global glngAutoNumber as Long

Public Function GetAutoNumber(BillNumber as Variant) As String

If BillNumber<>gvarBillNumber Then
gvarBillNumber=BillNumber
glngAutoNumber=0
Else
glngAutoNumber=glngAutoNumber+1
End If

GetAutoNumber=Format(glngAutoNumber,&quot;000&quot

End If

In your query, place a field -

InstanceNumber: GetAutoNumber([BillNumber])

Where BillNumber is the field in your table.