consecutive numbering

[bryan]

I am responsible for preparing invoices on a monthly
basis. I am trying to set up a database to track the
status of the invoices. Every month part of the invoice
# changes but the last three digits will be consecutive
from begining to end of the year. Is there a way when i
set up the form to have this number automatically
increment and still be able to modify manually.
Example-
AD-0401-001, Jan is 0401, Feb is 0402, march is 0403...
the 001 will progress to 999 through the year.

 

[Roxie Aho]

Assumption is that you have an AUTONUMBER InvoiceID field,
an InvoiceDate field that defaults to Date() and an
InvoiceNumber field in your table.

In the ON ENTER property for the InvoiceNumber field in
your data entry form, try:

Private Sub InvoiceNumber_Enter()
Me.InvoiceNumber = "AD-" & Right(Year([InvoiceDate]),
2) & IIf(Len(Month([InvoiceDate])) = 1, "0" & Month
([InvoiceDate]), Month([InvoiceDate])) & "-" & IIf(Len
([InvoiceID]) > 2, [InvoiceID], IIf(Len([InvoiceID]) >
1, "0" & [InvoiceID], "00" & [InvoiceID]))
End Sub

Roxie Aho
roxiea(delete)@usinternet.com

 

[Larry Daugherty]

Don't get sucked into using Autonumber as a component of your invoice
number. Autonumbers exist solely for the purpose of generating a unique ID.
They are not guaranteed to be sequential and they can go random and go
negative. The long and the short of it is that you should never use an
autonumber for anything that a user will see. If you do you'll come to
grief more often than not.. Generate and maintain your own numeric
sequence. That puts you in control of that process.

HTH

-- -Larry-
--

Assumption is that you have an AUTONUMBER InvoiceID field,
an InvoiceDate field that defaults to Date() and an
InvoiceNumber field in your table.

In the ON ENTER property for the InvoiceNumber field in
your data entry form, try:

Private Sub InvoiceNumber_Enter()
Me.InvoiceNumber = "AD-" & Right(Year([InvoiceDate]),
2) & IIf(Len(Month([InvoiceDate])) = 1, "0" & Month
([InvoiceDate]), Month([InvoiceDate])) & "-" & IIf(Len
([InvoiceID]) > 2, [InvoiceID], IIf(Len([InvoiceID]) >
1, "0" & [InvoiceID], "00" & [InvoiceID]))
End Sub

Roxie Aho
roxiea(delete)@usinternet.com

-----Original Message-----
I am responsible for preparing invoices on a monthly
basis. I am trying to set up a database to track the
status of the invoices. Every month part of the invoice
# changes but the last three digits will be consecutive
from begining to end of the year. Is there a way when i
set up the form to have this number automatically
increment and still be able to modify manually.
Example-
AD-0401-001, Jan is 0401, Feb is 0402, march is 0403...
the 001 will progress to 999 through the year.
.