Suggest an expression for changing a text string

[Curmudgeon]

Hello, everyone. I want to add a zero ("0") to the front of every
4-digit number in a text-formatted field to produce a 5-digit zip code.
Problem: most of my numbers are already 5-digit zips, and I fear that
adding a "0" via an update query will change them to 6-digits. Can you
suggest an expression that will produce the desired results?

 

[Douglas J. Steele]

Assuming that the data type for your zip code field is text, try:

UPDATE MyTable SET Zip = Right("00000" & [Zip], 5)

That'll correct any 3 digit ones that might exist as well (sorry, I don't
live in the US, so I don't know whether that's possible)

 

[Curmudgeon]

Doug, please accept the following from an unaccomplished ACCESS user:
Brilliant, absolutely brilliant! Works like a charm.

We don't have 3-digit postal codes here, just 5-digit ones with
optional 4-digit extensions for higher resolution.

 

[Douglas J. Steele]

In that case, it might be more efficient to use

UPDATE MyTable
SET Zip = "0" & [Zip]
WHERE Len([Zip]) = 4