Date based reference number

[shockerty]

Hi all,



I am trying to generate a date based reference number for deliveries. The reference needs 8 digits; the first 6 from the date and the last 2 as increments from 01.

e.g.
The 3rd delivery today would have the number 31081403

I have a table with:

• ID (Autonumber)
• DeliveryDate (Short Date)
• DeliveredBy (Lookup to couriers)
• Sender (Lookup to suppliers)
• FAO (Lookup to staff)
• SenderRef (Short Text)
• Notes (Long Text)

I then created a query from that table with:

• ID
• DeliveryDate

And the expression:

• DateNum: CStr(Format([DeliveryDate],"ddmmyy"))

This has given me a 6 digit string for the date. Not sure if I've gone down the right track or what to do next!

Any ideas?

Thanks

[orange]

Just curious why you "need" this?
Often easier to have -- 1 fact 1 field and concatenate such fields as needed if/when necessary for display.
Why do you need the sequence/increment number?

I'm not saying you can't, I'm trying to determine what you need.
Can you show us a jpg of your tables and relationships?
Can you describe this database in more detail?

[shockerty]

I am taking a legacy paper reference format and trying to make a database. There are often multiple deliveries in a day and so the increment is needed.

I think i will probably use the (autonumber) ID as the primary key, however end users will need to be able to search/sort using the 8 digit number.


Does that make sense?

[orange]

It might be easier for readers to understand your situation if you could expand on your description.
The old who, what , where, when, how much and how often sort of approach.
If autonumbers are to mean anything to the user, then I suggest you do not use them for your intended purpose.

Read this before deciding on autonumbers.

[shockerty]

The autonumber would just be used as the primary key in the Delivery table as it could be the only unique value in a record on a specific day. The end user would not need to know about that as the unique 8 digit reference number would be the identifier in their experience. But unless I can somehow generate that number within the table I assume the autonumber is the best primary key for the table.

I only created the DeliveryNumber query to generate the 6 digit "date code". It may be I have gone down the wrong track by doing so.

The purpose of the database is to keep a record of everything we have delivered so that for example:
-We want to see all deliveries that came in on ##/##/##
-We have a delivery number ######## and want to know when it arrived and what it contained
-etc

There will also be a table DeliveryItems that will have a lookup column to select the primary key from Delivery, and contain information such as part number, description and quantity. This way one delivery can contain many different items, or just one.

[June7]

A unique identifier meaningful to users would be something like SSN, OrderNumber, InvoiceNumber. The autonumber could be made to look like a fixed length Order or Invoice number but if no gaps in sequence is important, that would not serve. Also, there is no guarantee autonumber will increment up or even be a positive, although I have never seen anything put positive incrementing. Generating custom unique identifiers is a common topic. Here is one for starters
https://www.accessforums.net/access/...ers-21361.html