Scenario (copied from a post in the MSAccess forums):

I have added this format “IGSP000000” to my AutoNumber field to automatically generate an incremental work order number. The format displays fine, but does not store the data as formatted. For example, I need the stored data for record 1 to appear as “IGSP000001”. Although the data is displayed in the field as“IGSP000001”, it is stored as “000001” without the letters “IGSP”.

How can I get the entire formatted number to store in this format “IGSP000001”?

Answer (the below answer is from healdem):

If it's an autonumbered column you can't. Autonumber columns are integer field types (well in reality they are integer sized as bigint/long datatype).

Is the IGSP a common prefix?.. ie is there any other prefix used?

If you only use IGSP then leave your design as is and always prefix the autonumber column with IGSP whenever the user sees it (ie. an expression such as IGSP: Format("IGSP" & [MyAutoNumberFieldname]), or remove the IGSP before storing the number.

The alternative is store the prefix separately as an additional column in the db. You request the whole reference, split it into its constituent parts and carry on as normal.

The third option is to create your custom numbering scheme and store the whole thing as one value. But that means you have to write your own routines to get the next available number for that prefix or whatever. If the number part has significance outside the system then you may well be best to use this technique. By significance I mean that you must have contiguous numbers with no gaps, or numbers out of sequence.

Just as an aside, you don't store formatted values in a db, you store values and then format them in the front end or the application that the user uses. Like all rules there are exceptions, but you will get off to a better start if you presume that formatting is the preserve of the front end, not the data store. You can do some formatting when you extract the data from the data store, but it should not be formatted IN the datastore. Formatting often results in textual/string representations of the data to a computer the currency value of £12,345.67p is not the same as 12345.67, attempting to store values as a text/string datatype will lead to all manner of problems, reduce the effectiveness of the query engine. Another one to watch for is dates. Store data in the base datatype dates in datetime, numbers in number and so on.