Generating Invoice Numbers In Reports/Queries

[Dave Gibson]

Hi,

Could anyone please assist me with the generation of Invoice numbers
in reports.

I have a set of monthly invoices that are printed from a report based
on a query. I wish to be able to nominate the starting invoice number
then have the Invoices incrementaly number themselves in the report.

The invoices are printed on separate pages by a page break on the
customer number.

Is this possible in the report, or, further back in the process in the
query that provides the data for the report.

Regards

 

[Duane Hookom]

IMHO you should be generating the invoice numbers prior to running a report.
Reports are for publishing your data, not updating it in any way.

 

[Joe]

Dave,

Once you nominate a starting invoice number (assuming you will be
using a number data type) you can use the DMax function to add
one number at a time to the highest number.

=DMax("InvoiceNum", "Invoices")+1

You can use it as the Default Value Property in a control on a
form.

HTH,

Joe

 

[Dave Gibson]

Joe,

Many thanks for taking the time to reply. However, I am a bit confused
as I do not have a form with the Invoices in it.

I have a query that acts as the data source for a report. The single
access report contains perhaps 40 invoices for 40 separate customers
with a page break in the report on the customer number.

I need to get some type of prompt to ask me for the 1st Invoice Number
then get the rest sequentially number in increments of 1. I do not
require or have a table with invoice numbers stored in it so I simply
wish to create them "on the fly". There is no requirement to store
historic invoices with numbers etc.

Can the DMax function be used in some form of control in a report
whereby it asks for a starting invoice number then allocates the rest
as required above.

Thx once again for taking the time to reply.

Regards
Dave

 

[Duane Hookom]

You can add a prompt in your query:

InvoiceNum: [Enter Starting Number]

Then add a text box in your report:
Name: txtRunSum
Control Source: =1
Running Sum: Over All
Visible: No
Then add another text box for the invoice number:
Name: txtInvoiceNumber
Control Source: =[txtRunSum]+[InvoiceNum] -1
Visible: Yes