Autonumber with a prefix and starting at a specific number

%3CLINGO-SUB%20id%3D%22lingo-sub-1432350%22%20slang%3D%22en-US%22%3EAutonumber%20with%20a%20prefix%20and%20starting%20at%20a%20specific%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1432350%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I've%20used%20Access%20for%20a%20number%20of%20years%2C%20but%20I'm%20stumped%20now.%26nbsp%3B%26nbsp%3B%20Making%20a%20database%20and%20need%20a%20primary%20key%20autonumber%20that%20has%20the%20prefix%20of%20CT%20and%20starts%20counting%20at%2021000.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Can%20I%20accomplish%20this%20in%20the%20%60format'%20field%3F%26nbsp%3B%26nbsp%3B%20if%20not%2C%20what's%20the%20easiest%20way%20to%20pull%20this%20off%3F%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20first%26nbsp%3B%20record%20is%20CT21000%2C%20second%20is%20CT21001%2C%20CT21002%2C%20etc.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1432350%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hi, I've used Access for a number of years, but I'm stumped now.   Making a database and need a primary key autonumber that has the prefix of CT and starts counting at 21000.    Can I accomplish this in the `format' field?   if not, what's the easiest way to pull this off?

In other words, first  record is CT21000, second is CT21001, CT21002, etc.

Thanks!

1 Reply
Highlighted

@craigmmm 

 

First off, AutoNumbers are a unique type of Long Integer. These are Numbers and do NOT include alpha characters. Therefore, to do what you plan, you will need to adapt one of two strategies, either of which could work. Choose the one you feel most comfortable with.

 

  • Continue to use the AutoNumber as you primary key. When you DISPLAY the AutoNumber (if you really want to do that) concatenate your prefix to it for DISPLAY ONLY. "CT" & [YourPrimaryKeyFieldGoesHere] 
  • Use a Text field instead. If you do that, you can store the prefix in the field along with the manually generated values you need.

There are pros and cons to both approaches, as you might expect.

 

AutoNumbers are designed to be unique and are best suited for INTERNAL USE ONLY in providing Primary Keys for tables in an Access relational database application. They cannot be guranteed to generate sequential values with no gaps, so you should not rely on them if sequential values without gaps is a requirement for your relational database application. However, they are by far the most reliable way to manage surrogate Primary Keys in tables, so they are usually the preferred option for that purpose.

 

If you decide you want to DISPLAY a sequential value and want to invest time in creating a VBA module to produce them, that's a different kind of task. Many people do use this approach for DISPLAYING sequential values to users for things like printing invoices, for example.

 

There are a number of examples of procedures to produce those sequential values on the internet. You'll find one of the best here.