• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1481
  • Last Modified:

Combine Text and Auto Number to create a unique ID

I need to create a unique ID in a table when an access record is created on a form.  
I want to combine a text string with the auto number so that the default auto numbers from different data sources  would not result in a duplicate.  
The text string would be the first two characters from one field and some zeros combined with the auto number.  I would like to control the field length so that it would sort alpha numerically in the order that the records were created.
IE:  Combine the fields [Location] and [ID] so the result is  Lo000001:Lo000999,Lo001000... etc.
Thanks again!
1 Solution
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I suggest instead you keep two separate fields, then create a Unique Index on the Auto Number and the Text String  ... this is much cleaner, and accomplishes the same thing.
AMixMasterAuthor Commented:
The text string would have hundreds of duplicates.  
I use a default value for the text string ( [Location] )for each remote database location.  
The auto number would have 1 dup for each table created remotely.  
We want to combine unique records.  
When appending to the main database, in order to prevent duplicates the append query table join would be set up...
That is the point where I thought that I would require a unique ID field
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now