Invoice Number Equals Current Year Plus One

[koolwaters]

Happy New Year Everyone!

We have a database we are using for invoicing and we want the invoice number to be the current year and an incremented number, but we want the incremented number to restart at 1 for each year (2015-01, 2015-02, 2015-03, 2015-04, 2016-01, 2016-02, etc.).

The code below worked fine for 2015 but I need some help in adjusting it for 2016. The last invoice number for 2016 is 781 so the current code returns 2016-0782 as the invoice number but we want it to be 2016-01 and then increment from there (2016-01, 2016-02, 2016-03, etc.).

Any suggestions?

Dim strNextInvoiceNo As Integer
Dim strInvoiceNo As String
strForm = "frmInvoice"
   
    strNextInvoiceNo = Nz(DMax("[LastInvoiceNo]", "tblInvoice"), 0) + 1
    strInvoiceNo = DatePart("yyyy", Now()) & "-" & "0" & strNextInvoiceNo
        
    DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
    Forms.frmInvoice.InvoiceNo = strInvoiceNo
    Forms.frmInvoice.LastInvoiceNo = strNextInvoiceNo

Thanks in advance!

 

[jorismoerings]

Hi,

Not sure what the design of your "tblinvoice" so can ony help by a few pointers.
You need a check performed BEFORE adding 1 to the StrNextInvoiceNo.
1. I assume there's a field in the DB in which you register the invoice date; create a variable holding the year part of the last invoice date (assume it's called [Invoicedate])
2. perform a test of the value of year([InvoiceDate]) against Datapart ("yyyy", Now())
3. if test = equal do LastInvoiceN0 + 1
4. if test <> equal do strNextInvoiceNo = 1

It will probably look something close to this.

Dim strNextInvoiceNo As Integer
Dim LastInvoiceYear as String
Dim strInvoiceNo As String
strForm = "frmInvoice"
   
    LastInvoiceYear = DMAX("year([Invoicedate])","tblInvoice")

    If LastInvoiceYear = datepart("YYYY", Now()) then
      strNextInvoiceNo = Nz(DMax("[LastInvoiceNo]", "tblInvoice"), 0) + 1
    Else
      strNextInvoiceNo = 1
    End if

    strInvoiceNo = DatePart("yyyy", Now()) & "-" & "0" & strNextInvoiceNo
        
    DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
    Forms.frmInvoice.InvoiceNo = strInvoiceNo
    Forms.frmInvoice.LastInvoiceNo = strNextInvoiceNo

Hope this helps.

 

[koolwaters]

It only worked for the first invoice.

When I attempted to try a second time, I get the following error:
The expression you entered as a query parameter produced this error: '[strLastInvoiceYear]'

The invoice date field uses a default value =Date()

 

[koolwaters]

What seems to be happening is that when it finds the year in the last invoice date for 2015, it adds 1. However, when the year in the invoice date is 2016, it looks for the highest invoice number that was used, which is 781 for 2015 and returns 2016-0782, which is incorrect.

 

[jorismoerings]

Hi,

Not sure why this error comes up. It could be because the year argument probably returns more than 1 value so to restrict that try changing this line :

LastInvoiceYear = DMAX("year([Invoicedate])","tblInvoice")

with

LastInvoiceYear = DMAX("year([Invoicedate])","tblInvoice", "Nz(DMax("[LastInvoiceNo]", "tblInvoice"), 0)")

 

[Joe4]

Here is a little different way you may want to consider using going forward:

Set up a table with two fields: Year (Primary Key field) and LastInvoiceNumber
Then make an entry for each year (i.e. 2015, 2016, and putting out some future years with a LastInvoiceNumber of 0)

Then, when you create an invoice create a VBA script or Update Query that looks up the LastInvoiceNumber by year, adds one, and updates the LastInvoiceNumber field in that table.

 

[koolwaters]

When I replace the code, I get a compile error:
Expected: list separator or )
and it highlights

[LastInvoiceNo]

 

[jorismoerings]

Hi,

Figured it out.
Try it like this:

Dim strNextInvoiceNo As Integer
Dim LastInvoiceYear As String
Dim strInvoiceNo As String

   
    LastInvoiceYear = DatePart("YYYY", (Nz(DMax("[InvoiceDate]", "tblInvoice"), 1)))
    LastInvoiceDate = Nz(DMax("[InvoiceDate]", "tblInvoice"), 1)
    CurrDate = DatePart("YYYY", Now())

    
    If LastInvoiceYear = CurrDate Then
      strNextInvoiceNo = Nz(DMax("LastInvoiceNo", "tblinvoice", "year([invoicedate])=year(now())"), 0) + 1
    Else
      strNextInvoiceNo = 1
    End If

    strInvoiceNo = DatePart("yyyy", Now()) & "-" & "0" & strNextInvoiceNo
    
    DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
    Forms.frmInvoice.InvoiceNo = strInvoiceNo
    Forms.frmInvoice.LastInvoiceNo = strNextInvoiceNo

 

[koolwaters]

Perfect! That does the job.

Thanks for your help:

Cheers!

 

[jorismoerings]

No problem, your welcome.
we got there in the end.
Glad i could help.