I have a field where the data should be 9 characters in length, however, some are only 8 (leading zero dropped upon import?)-how do I add a leading zero to those records with only eight characters while leaving those with nine characters alone?

---

Answer from Richard Rost:

First, convert the field to TEXT.

Next, create a query to pull only records with 8 characters.

MyLen: LEN(MyFieldName)
Criteria: 8

Once you have these results, turn it into an UPDATE query, and then set the Update To value for the field itself to:

="0" & MyFieldName

This should add the extra "0" on for you. Make sure you only run it ONCE.

You can use an Update Query to change records in a table.

See this FREE tutorial on my website for more information and step by step instructions.  

http://599cd.com/tips/access/update-query?key=BlFAlEx