Numbering Sequence for Invoice

[nmeuvdast8]

Hi There!

I have a question. I have a table, tblMeterReading with the following fields...
ReadingID
ReadingDate
ContractID
BillingNumber
Reading

I use this code to assign sequential values to BillingNumber per ContractID...

Dim L As Long
If IsNull(ReadingID) Then Exit Sub
L = DCount("ReadingID", "tblMeterReading", "ReadingID <>" & [ReadingID] & " And [ContractID] = " & Me.SerialCombo & "")
If L = 0 Then
L = 0
Else


L = L + 1
End If
BillingNo = Year(ReadingDate) & "-" & Format(L, "0000")

This code assigns the sequential number to BillingNumber AfterUpdate of SerialCombo in YYYY-0000 format. The code works fine, but here is the problem...

My plan is just to import a few months data and have the numbering sequence continued from the last number since some of the older Readings can no longer be recovered. Since I am using DCount for this numbering, I cannot have just a few readings for each of the ContractID because that will definitely show a low count which wont match the last number of the sequence.

Example
2023-0001, 2023-0002,......2024-0024

If I record only say 3 previous readings, then the BillingNumber will be assigned as 2025-0004 because the DCount will return a value of only 3.

Is there a way to implement this via DMax instead of DCount?

I tried using DMax, but I get a type mismatch error since I have formatted the BillingNumber as String with the code BillingNo = Year(ReadingDate) & "-" & Format(L, "0000")

Thanks for the time and help!

[Welshgasman]

Surround your Year(ReadingDate) & "-" & Format(L, "0000") with single quotes '
Have a field that is the last invoice number in a table and then retrieve that and add 1 and write back when the invoice has been saved.

[jojowhite]

You can of course you DMax()

Dim strYear As String
Dim lngSeries As Long
Dim ret As Variant
strYear = Year(Date) & "-"


ret = DMax("BillNo", "tblMeterReading", "[ContractID] = " & Me.SerialCombo & " And BillNo Like '" & strYear & "*'") 
If IsNull(ret) Then
    BillNo = strYear & "0001"


Else


    ret = Replace$(ret, strYear, "")
    ret = Val(ret)+1


    BillNo = strYear & Format$(ret, "0000")
End If

[nmeuvdast8]

I tried adding a new field, Numbering in the table tblMeterReading. and used this code....

Private Sub serialCombo_AfterUpdate()Dim L As Long
If IsNull(Numbering) Then Exit Sub
L = Nz((DMax("Numbering", "tblMeterReading", "ReadingID <>" & [ReadingID] & " And [ContractID] = " & Me.SerialCombo & "")), 0)
If L = 0 Then
L = 0
Else
L = L + 1
End If
Numbering = L
BillingNo = Year(ReadingDate) & "-" & Format(L, "0000")

End Sub

Worked fine, however, the Numbering field do not get any value saved (from this code, Numbering = L). Its always blank, so I always get the BillingNo as 2025-0000. Cant figure out why the Numbering wont get saved and updated using this Numbering = L while the BillingNo gets saved and updated using BillingNo = Year(ReadingDate) & "-" & Format(L, "0000")

[jojowhite]

use your BillingNumber, and modify the code in post #5.
replace all occurrence of BillNo with BillingNumber.

[nmeuvdast8]

tried it, but it didnt work. I have a few sample data in my table. For most of the data the BillingNumber was always set to 2025-0001, only one of the data was assigned a correct BillingNo

[jojowhite]

if you have small number of records on your tblMeterReading (all for the year 2025),
you can Reset all BillingNumber to blank and Re-number them using VBA.

see this demo first.
see tblMeterReading first and see that there is correct billing number(?)
based from your code you want them serialized by ContractID, is that right.
so for contract 1, the serial will start with 1, contract 2 also 1 and so on.

run qryRemoveAllBillingNumber query to remove the BillingNumber.
then open Module1 and run UpdateBillingNumber() to assign them
the billing number. Is the series correct for each Contract?
If the answer is no, then what series do you need?
continuous for the year and doesn't care for contractID?

[nmeuvdast8]

Got it to work, I used both Dmax and Dcount. I used Dmax to get the maximum value of the Numbering field of my tblMeterReading and DCount to count if there are prior values in the meter reading for the said ContractID. This way, if the Dcount and Dmax were 0, the Billing Number would start as YYYY-0000 else, it would increment by 1. I wanted to start at 0000 for initial readings since these wont be sent as billings

Private Sub serialCombo_AfterUpdate()
Dim L As Long
Dim M As Long
M = DCount("ReadingID", "tblMeterReading", "ReadingID <>" & [ReadingID] & " And [ContractID] = " & Me.SerialCombo & "")
L = Nz((DMax("Numbering", "tblMeterReading", "ReadingID <>" & [ReadingID] & " And [ContractID] = " & Me.SerialCombo & "")), 0)
If M = 0 And L = 0 Then
L = 0
Else
L = L + 1
End If
Numbering = L
BillingNo = Year(ReadingDate) & "-" & Format(L, "0000")

End Sub

Also resolved the issue that the Numbering field doest get updated. I ended up adding a textbox to the form for Numbering. Thanks for the help!

[jojowhite]

so, what happens next year BillingNo?
the Year part will change, but the serial part will
continue to grow?

you only think about this year. you also need to
think of the many years to come.

also with your current code, you will have Same
BillingNo for different ContractID.

[nmeuvdast8]

Yes the year part will change and the serial part will grow. Also for the same billing number for different contractIDs, its ok since it can be diffrentiated by the ContractID. Or I can include the ContractID in the billingNo format. Thanks!