Invoice number

[slik_02]

Hi Guys,

This is my first thread and ill try to be descriptive as possible. I want to create an auto generated invoice number for...invoices. But i also want it to follow a certain formula.



I want the format of the sequence to go like this: ###.@@@. Basically i want the ### to represent the total number of invoices from a certain client and the @@@ to represent the total number of invoices for all clients

Since I will be recording all invoices from the year 2000, I feel it is important that it auto-adjust based on date. I feel this option would make recording easier. To expand on this characteristic for clarity, basically i want it to adjust the invoice number accordingly if I input an invoice that occured before another registerd invoice. For example, their exist an invoice recorded in the access databse with an invoice sequence of 005.010 and a sent date at 2-12-09 ( so using the format that means its the 5th invoice for a client, and 10th overall for all clients). Now, lets say I want to record another invoice that was dated 2-11-09 that was either by the same company or different company. I would like the result to be that the recorded invoice of 2-12-09 be moved down to 006.011 (if it were the same company) or .005.011 (if it were another company).

Basically if its possible to simplify the explanation, its an id counter for the client and id counter for the whole database. I just want it to adjust based on date and place side by side.

My database setup goes like this. I have a form that records project details and is entitled project details. Then I have my subform where i want it to record the invoice numbers. after everything is filled up in the subforms of project details, pressing print invoice will show the invoice. and i want to invoice numbers to appear in the invoice.

I am using MS Access 2007. Thank you guys for your help. I hope was descriptive enough.

[RuralGuy]

Are you honestly suggesting changing an invoice number after it is published? You completely defeat the reason for an invoice number if you do that. You are putting way too much information into an invoice number. The counts you want can be printed on an invoice if you desire but I would not recommend using them as part of your invoice number.

[slik_02]

well, heres the thing. in a sense they were bad invoices cause it lacked invoice numbers to begin with. and a lot of the old invoices just had 1 or 2 or 3 as an invoice number. and they were all saved in the filing cabinet never on the computer. why is it like that? beats me. but i want to fix it and also be able record 'em properly so i can write up reports and study the company i am working in.

[RuralGuy]

DMax() with the client as the criteria will return the current count of client invoices and DCount() without a criteria will return the total number of invoices.