Create Invoice Number using year, month, day and a number that increments with one

[chrisbenett]

Hi all,
I want to create an invoice number that looks like "YYMMDD01" which will increment with 1 for each invoice. But the next day, it will need to start at one again. Example:

Today: 13031600, 13031601,13031602
Tommorow: 13031700,13031701,13031702

[Seth Schrock]

Might I suggest that you use a four digit year. Otherwise, the first invoice on January 1st 2013 will have the same invoice number as January 1st 2113. Or you could automatically delete invoices over 99 years old.

Have you tried anything?

[chrisbenett]

I'll take that into consideration. Thanks.
I didn't try anything yet.

[zmbd]

chrisbenett:
There are several examples within this site covering just exactly this topic.
My advice is as follows:
In your table - let's call it [tbl_invoice]
[tbl_invoice]![invoice_PK] autonumber and primary key
[tbl_invoice]![invoice_ProductionDate] date/time
[tbl_invoice]![invoice_seq] numeric long
(other fields for [tbl_invoice] as needed)
Now when you insert a record I would first pull the records for the date in question and then I would find the highest number in [tbl_invoice]![invoice_seq] for that date. You can do this using a few methods; however in this case I would use the domain function DMAX() (Domain Functions (v2007))with the criteria based on the date and returning the [tbl_invoice]![invoice_seq] value. If you have really huge data table I would set up the query to filter out the date first and then dmax() on that query; however, I have a table with several hundred (actually in the thousands) that using the basic dmax() on the table works fairly fast even with a split database over a LAN connection.

Now that you have that last [invoice_seq] value; create the next record and increment the sequence by one.

Now as for your invoice: to generate the lot number, in your query or the report, create a calculated field that combines the [tbl_invoice]![invoice_ProductionDate] formatted as you desire, and the [tbl_invoice]![invoice_seq]. The nice thing here is that if you have to change the invoice number format you only change the calculated field. Or say you need a custom invoice number for only one or two of your customers, then you can add conditionals and so forth.

You can do as Seth has suggested about the four digit year (and I second this) However, you need to consider what the life span of the numbering system is. For Tax records... I'd make sure that had a 20 year uniqueness at minimum. For something like we have in my lab the lot numbering system cycles thru based on the last number of the year... but anything that's already 10 years old has long since been discarded.

I have not provided code nor SQL at this point because it's always better to let the person asking the question try to solve the problem first; however, if you run into a specific issue, directly related to this thread, then please post back and we can take the next step.

[chrisbenett]

Sorry for taking so long. I was caught up with other projects.
Are you suggesting to create a separate table or adding those fields to my existing table within access?

[NeoPa]

The same table. I'm not sure having them in separate fields is necessary myself, but it certainly could work that way.

I tend to use DMax() with a Criteria that matches the date part of the value and an Expr that returns the numeric part. The returned value should be converted from a string using something like Val() and a lack of any entries found should be handled by using Nz(..., 0).

NB. No-one is likely to throw any code your way until you've shown preparedness to make the effort on your own behalf first.