How can I generate auto numbers with dates?

A

Ancientt

I would like to generate auto numbers with a combination of date and number,
for example: Month+Year+number 1004001
 
D

DevalilaJohn

You'll need to provide some more information. You can get the month and year
out of the format function

Format(Date,"mmyy")

Where the number comes from is up to you. That's the part that needs more
info.
 
D

Douglas J. Steele

Why do you want them? It's actually a violation of relational database
theory to combine more than one piece of information into a single field.
You'd be better off storing the date and the Autonumber as two separate
fields. If you really need to combine them for some reason, concatenate them
in a query, and use the query where you would otherwise have used the table.
 
B

Brian

This is a round-about way but would work. In your table create a field for a
data type "AutoNumber". This will give an automatically assigned number to
each record. Then create an Update query to concatenate the fields required
in the order you want.

Ex. a table with three fields:
Test=Table name
A=Autonumber field
B=Date field
C=Date and Number field

Create an Update query:
1.) Add the test table then add the "C" data field to the query.
2.) Change query setting to an "Update" type query.
3.) Now in the "Change To" field add the concatenate string.
[test]!&[test]![A]
This will give a result of DateAutonumber

If a dash "-" or space " " is required add it to the string.
[test]!&"-"&[test]![A]
This will give a result of Date-Autonumber

Then add a "DoCmd" to run the quire either "On Open" or assign it to a
button in a form for manual update.

Hope this helps.
 
Top