Special Number Sequencing - Like Invoice Numbers

[Brigham Siton]

We want to be able to generate invoice number in squencial order using the
current date as the basis.

For example, when I hit the button "generate invoice", then it will pick up
the date & time format like: 0307141245

03 - year
07 - month
14 - day
12 - hour
45 - minute

Can this be done? Thank you.

 

[Rick Brandt]

We want to be able to generate invoice number in squencial order using the
current date as the basis.

For example, when I hit the button "generate invoice", then it will pick up
the date & time format like: 0307141245

03 - year
07 - month
14 - day
12 - hour
45 - minute

Can this be done? Thank you.

Me!InvoiceNumber = Format(Now(),"yymmddhhnn")

Are you sure you will never have people press this twice in the same minute? Also,
different users could have their system time slightly different in which case you
could easily have multiple presses generate the same number. This will work fine in
a single user application though.

 

[Brigham Siton]

Thanks for the prompt reply.

Ouch, that is a good point. There are 3 people that will be working on
this.

Can I have a control that when the system generates a duplicate invoice that
it prompts for the user to generate a new one?

Brigham

 

[chriske911]

just add an autonumber field so the records will always be unique and then
rethink the numbering of your invoices, you will need something else besides
the date since a date will already be a part of the document

in most invoice programs you will see there is a special crafted
autonumbering field used for the invoices and no way to break the
autonumbering, this is mostly by law required to prevent tampering with
invoices
this autonumbering can be created by concatenating several values or with a
function behind your forms

bon chance

 

[Rick Brandt]

Thanks for the prompt reply.

Ouch, that is a good point. There are 3 people that will be working on
this.

Can I have a control that when the system generates a duplicate invoice that
it prompts for the user to generate a new one?

Brigham

What you could do is use "for display" an invoice number that is as you
described, but which has an incremental number tacked onto the end. Then you
could "store" the creation date and time in a DateTime field along with an
integer field that is incremented throughout each day and starts over the next
day. All that would be necessary then would be to calculate the max value of
the integer field with a date of Today and add one to it.

In form's BeforeUpdate event

If IsNull(Me!NumberFieldName) = True Then
Me!NumberFieldName = Nz(DMax("NumberFieldName","YourTableName",
"DateValue(DateField) = Date()"),0)+1
End If

On your forms and reports display the invoice number with the following
expression:

=Format(DateField,"yymmddhhnn") & Format(NumberFieldName, "00")

The above would be good for 99 invoices per day. If you thought you might
exceed that, you could use "000" for the second part.