Combine Text and Auto Number to create a unique ID

Posted on 2012-08-19
Last Modified: 2012-08-19
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!
Question by:AMixMaster
    LVL 75

    Accepted Solution

    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.

    Author Closing Comment

    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

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now