How do I create an invoice number in my database?

[Tony Williams]

I am using the Microsoft Access Time and Billing database as a start for my
own database. I need to change various fields because I am in the UK and I
want to charge by the day not the hour. However although the database will
create an invoice as a report it doesn't save the invoices. There is a
provision for an invoice number which I want to be ClientNbr/Invoice Nbr eg
0056/0001 but this is input with each new invoice and not stored. I would
like to store details of the invoices I issue and also have the invoice
number automaticaly generated. The invoice number must start at 0001 for
each client rather than it being sequential across all clients. I am
reasonably OK at building an extra table but need pointing in the right
direction. The sample database doesn't have an Invoice table. Could someone
start me off please? Any help would be appreciated.
Thanks
Tony

 

[Ken Snell [MVP]]

Your new table that holds invoice numbers needs two fields that will serve
as the composite primary key:
ClientNbr
InvoiceNbr

To get the next sequential number for a client:
NextClientInvoiceNumber = Format(Nz(DMax("InvoiceNbr", "TableName",
"ClientNbr=" & ClientNumberValue), 0) + 1), "0000")

Above assumes that ClientNbr is a numeric field.

 

[Tony Williams]

Thanks Ken, appreciate that.
Tony

Your new table that holds invoice numbers needs two fields that will serve
as the composite primary key:
ClientNbr
InvoiceNbr

To get the next sequential number for a client:
NextClientInvoiceNumber = Format(Nz(DMax("InvoiceNbr", "TableName",
"ClientNbr=" & ClientNumberValue), 0) + 1), "0000")

Above assumes that ClientNbr is a numeric field.

 

[Tony Williams]

Hi ken I have a problem here is my formula
= Format(Nz(DMax("InvNbr", "Invoices","ClientNbr=" & ClientID), 0) + 1),
"0000")

However I am getting an syntax error message saying I must include text data
in quotes but all my fields are numbers? I am using this formula as an
expression in a query so it looks like this
NextNbr: Format(Nz(DMax("InvNbr", "Invoices","ClientNbr=" & ClientID), 0) +
1), "0000")

Can you see where I'm going wrong?
Thanks
Tony

 

[Tony Williams]

Ken I've tried it as the formula for an unbound control on the print invoice
form of the sample database and it still gives me the same message?
Tony

 

[Ken Snell [MVP]]

Is the ClientNbr field a numeric field or a text field? If it's a text
field, then your expression needs to delimit the value of ClientID with '
characters:

NextNbr: Format(Nz(DMax("InvNbr", "Invoices","ClientNbr='" & ClientID &
"'"), 0) +
1), "0000")

 

[Ken Snell [MVP]]

It's not completely clear what you want to do, but yes you can put
"available" values into a table (I'd put all values in the table, and then
include a field named "Used" as a boolean field and use that to know when
the value has been used), use a query to get the "unused" values from that
table as the Row Source for a combo box, use the Combo Box's AfterUpdate
event (or another event) to mark that value as "used" in the table.

 

[msnews]

Á¦°¡ ¿µ¾î¼öÁØÀÌ ¾àÇؼ­ ´Ù ¸ø¾Ë¾Æº¸´Âµ¥ Ȥ½Ã Çѱ¹¾î·Î º¼¼ö ¾øÀ»±î¿ä Áß±¹
½Ã½ºÅÛ¿¡ ¾Æ¿ô·ÏÃß°¡ÇÏ¸é ¹Ù·Î Áß±¹¾î·Î ¶ß´Âµ¥ Çѱ¹ ½Ã½ºÅÛ¿¡¼­´Â ¿µ¾î·Î ¶ß³×¿ä
¾î¶»°Ô ÇؾßÁÒ

 

[nellie almodovar]

Ken,

Thanks ...

I have created followings

Table 1 - The master data base - fields are : Ref / Invoice No / Issued date
(invoice no is a list box , source from Query 1

Table 2 - Pre-assigned invoice nomber - Invoice no / Ref
Query 1 - To query out the invoice with out Ref.

As one invoice will need one Ref, can not be null or 0.

I managed to sort out the invoice number with ref. However, it does not
update Table 2 , after i selected the invoice number. As it supposed being
taken so other use can not use it.

Ken, hope you can help me

Eric

 

[Ken Snell [MVP]]

Can you post the code that you're using to get the next invoice number,
etc.? It's not possible for me to suggest something without knowing what
you're doing and how you're trying to update Table 2.

--

Ken Snell
<MS ACCESS MVP>