Custom Order Number that Includes the Date

[Guest]

I would like to create an order number format that includes the date.

yyyymmddxxx, where xxx is the order number for that particular day,
beginning at 001

For today:
20061114001, then 20061114002, then 20061114003 and so on...

Can this be done?

 

[Graham Mandeno]

Hi Cheese

Let's assume you already have an OrderDate field. It would be better to
have a field named OrderSequence which contains only the sequence number
(xxx) for that day. Make the two fields together a unique composite key (it
could be your primary key, although personally I avoid composite primary
keys if the given table is going to be on the "one" side of a relationship,
so I would add an autonumber primary key).

To allocate a new sequence number, use DMax to look up the highest value
that has been used so far today (substitute zero if the isn't one) and then
add one:

OrderSequence = Nz(DMax( "OrderSequence", "Orders", _
"OrderDate=Date()"), 0) + 1

To *display* the order number, use an expression in a query or in the
ControlSource of a textbox:

=Format([OrderDate], "yyyymmdd") & Format( [OrderSequence], "000")

 

[Guest]

Sure can Cheese!!

Use format(date(), "yyyymmdd") to get the date in that format, and append
the counter to the end. Use dlookup to find if there is already an item with
that date in teh left 8 chars - if so, find the max of the remaining chars to
work out the largest available number and add one.

Hope that helps.

Damian.