Fix an AutoNumber Prefix in a Microsoft Access Form
Question details
The user needs an Access form to display an AutoNumber with a prefix, such as NJ10401, instead of showing only the numeric portion.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A form displays an AutoNumber field that should appear with a text prefix.
- Observed behavior
- The form displays only the numeric suffix even though a prefixed format may be set on the table field.
Open the form in Design View and identify the text box bound to the AutoNumber field. Check an existing record's stored number so you can tell whether the desired display also requires an offset.
Apply the Prefix Format to the Bound Form Control
Use this when the stored AutoNumber already has the numeric value you want to display after the prefix.
An AutoNumber stores a numeric identifier; its Format property changes only how the value appears. A format set on the table field may not be applied to an existing form control.
In the Navigation Pane, right-click the form and select Design View.
Click the text box that displays the AutoNumber, then open Property Sheet and check its Control Source on the Data tab.
On the Property Sheet's Format tab, enter "NJ"00000 in the Format property. A stored value of 10401 will display as NJ10401.
Save the form, switch to Form View, and check an existing record to confirm that the prefix appears.
Display a Prefix and Numeric Offset in a Calculated Text Box
Use a calculated text box when the displayed number must differ from the stored AutoNumber, such as displaying NJ10401 for a stored value of 1.
Work with Office Documents in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface for working with documents, spreadsheets, and presentations. It can help you move common Office files into a new workflow, but it does not fix Access form formatting or replace an Access database.

Frequently Asked Questions
Why does the prefix appear in the table but not on the form?
An existing form text box may not use the table field's Format property. Open the form in Design View and set the bound text box's Format property on its Property Sheet.
Does adding a prefix change the AutoNumber stored in Access?
No. A format such as "NJ"00000 changes the displayed value only. The AutoNumber remains numeric for keys and relationships.
How can I display NJ10401 when the AutoNumber is 1?
Add a calculated text box and set its Control Source to ="NJ" & Format([Job Number]+10400,"00000"), replacing Job Number with your field name. This changes the display, not the stored value.
Will AutoNumber values always be consecutive?
No. AutoNumber values can have gaps, so use them as identifiers rather than relying on them for an uninterrupted sequence.




