Create Number From Date

[John W]

I'm importing large amounts of text into Access and I want to assign a
"trace" number to each record. I'd like the trace number to start with the
date but in just plain number format. Then I'd like the last part of the
number to be automatically incremented. For example records imported on
11/28/07 would have an ID number like:
200711280701
200711280702
200711280703...
How can I do this?

Thanks for the help!

 

[John W. Vinson]

I'm importing large amounts of text into Access and I want to assign a
"trace" number to each record. I'd like the trace number to start with the
date but in just plain number format. Then I'd like the last part of the
number to be automatically incremented. For example records imported on
11/28/07 would have an ID number like:
200711280701
200711280702
200711280703...
How can I do this?

Thanks for the help!

How are you doing the import? It may be more appropriate to use *two* fields -
an Autonumber which will increment for all records, and a DateImported field
with a default value of Date(). This would let you use File... Get External
Data... Link to link to your text file, and run an Append query to populate
the table, automatically filling in these two additional fields.

Your trace number isn't ideally designed: fields should be "atomic", having
only one meaning. You're assigning two. It would be easy to append the date
and autonumber fields for display purposes if that's needed; in addition, the
Autonumber will have a range into the billions, whereas your format limits you
to 10000 records (hardly "large amounts"!)

John W. Vinson [MVP]

 

[John W]

The import files are flat text with multiple delimiter types and data going
to multiple tables - everything is imported using VBA. I'm creating a
database that will import files from our clients. Each file will have
records that I want the client to be able to call and check on. The reason
I wanted this trace number to start with a date would be for easy reference.
If a client called in with a trace number I could know when we received
something instantly. I think I'll take your suggestion and append the date
to the autonumber since I do have a large volume

Thanks Again!

 

[John W. Vinson]

The import files are flat text with multiple delimiter types and data going
to multiple tables - everything is imported using VBA. I'm creating a
database that will import files from our clients. Each file will have
records that I want the client to be able to call and check on. The reason
I wanted this trace number to start with a date would be for easy reference.
If a client called in with a trace number I could know when we received
something instantly. I think I'll take your suggestion and append the date
to the autonumber since I do have a large volume

Just do use *two separate fields* rather than constructing a "portmanteau"
field containing both the date and the autonumber. Your VBA code could simply
open a recordset based on the table and append to it.

John W. Vinson [MVP]