Generate Automatic Number

I have an Access database and need to generate an Invoice number in the following way:
"RPI"("YYMM")000 (Autoincrease)= for example:"RPI0306000" for the first invoice generated on June 2003. RPI0306001 (for the second, etc, etc...)
ok, let me explain what this means: RPI should be default (I know how to do that) the problem begins when I want to enter the Year and the Month. I don't want the system to determine these based on the current date. I want these to be determined based on the date I INPUT. How? there is a field called DATE on the database. I want to use the data inputed there and then add a "000" for the first invoice that month "001" for the second "002" for thethird, so on and so forth..
Does anyone know how can I do this?

Please help me if you do.
Thanks.

Gleiry

So you basically need a concatenation of 3 things: (1) "RPT", (2) a string in the form "yymm", (3) a number giving the number of current invoices for the month+1.

(1) is done. For (2) if you already have a field with the date in it, you need to create the string based on it in the form "yymm". For that, use the Format function like this: Format(Me.txtDate, "yymm")

For (3), use a DCount function to find the number of invoices that have already been entered for the month and year corresponding to the month and year in your data field from (2). Then you need to prepend some zeroes to it. If you need help with the Dcount function, let me know and I'll help you with the syntax. Once you have the number, use a formula like this to get the correct number of leading zeroes: Right("000" & Me.txtInvoiceCount,3) where I assume Me.txtInvoiceCount is the name of a field containing the result of the Dcount function. And it assumes you won't have more than 999 invoices a month.

FYI: Many people (including me) like to avoid the D functions (like DCount) because they're often quite slow. They can be simpler to use than some other techniques though, especially for beginning Access users. The alternative is to create a custom function where you open the table or query containing your invoice numbers as a recordset and do a count of the invoices there, returning the count as the result of the function call. It's not as bad as it sounds, really.

Gleiry, Pat is right about using the Dmax function instead of DCount.

Pat, Gleiry didn't mention anything about using the calculated field as the primary key, but you are right to caution against this type of usage.

Primary key

I have several sub databases, each with information specific for a certain section (physicians, cases, etc.) These are linked to a master DB so the principal person can analyze data across sections as a group and come up with aggregate data. tblPhysician has an autonumber as the primary key field (there are reasons for not using dictating number or another number). Along the lines of this thread, I need a way to differentiate the primary key so that when they dump into the master DB, there won't be duplicate primary keys, for example physician 1 from Emergency & Physician 1 from Ortho. tblPhysician does have a section code, so I would like to use a combination of that and the autonumber feature(i.e. maybe 1-1 & 2-1 or E1 and O1). Is a function needed, or is there a simpler way?

you could have a simple query to ask the SQL database or Access DB that has WHERE invno= Like("RPI0306*") and count the returns! since your starting at 000 that means you next value would be how many you currently have (001)

Not sure about the Dmax() part

I know this thread is a couple of years old, but it describes exactly what I am trying to do.

dcx693 said:

So you basically need a concatenation of 3 things: (1) "RPT", (2) a string in the form "yymm", (3) a number giving the number of current invoices for the month+1.

Click to expand...

I do not understand how to use DMAX to determine the next available number for the "YYMM" combination. Could someone please explain?

This is what I am using so far, but obviously it doesn't add the last four digit autonumber sequence.

Thanks for the help.