How to Add Leading Zeros in a Text Field in Microsoft Access
By Emily Ediger
Customize your Microsoft Access queries by adding expressions to your fields. Expressions calculate information and display the result in the query's datasheet view or any object that relies on that query. Once you add an expression, that field can't be updated, so it's best to use expression fields in combination with input fields. With expressions you can automate calculations and speed up database processes. If you need to add leading zeros to a text field, you can do so with an expression.
Step 1
Open your query in "Design View" by right-clicking the title in the "Navigation Pane" and selecting "Design View." You can add an expression to queries, forms and reports but queries will display the modified text in all objects that depend on it. If you add an expression to a form or report, it will only display the modified field in that object.
Step 2
Add a new field to display the text field with leading zeros. You need to provide a name that is different from the original field name, or else you will encounter a circular reference error. For example, to add a leading zero to a "ProductNumber" field, name the field "NewProductNumber."
Step 3
Type in a colon after the field name and the following expression: "0"&[Field1]. Add the appropriate amount of zeros and replace "Field1" with the original field name. For example, your expression may look like this: NewProductNumber: "000"&[ProductNumber]
Step 4
Save the query and return to "Datasheet View" of the query. Review the update to ensure it is properly formatted. If you need to modify the expression, return to "Design View" and make the necessary changes.
References
Tips
- You can add a combination of numbers and letters to your expression by modifying the characters in the set of quotation marks.
Writer Bio
Emily Ediger began writing professionally in 2007. Her work includes documenting technical procedures and editing event programs. Her expertise lies in technology, interactive learning and information retrieval. She holds a Bachelor of Arts in English from Portland State University.