Adding a leading zero via update query in Access table

Question

I have a table with a column that contains 3 digit zip codes stored as text, and some of them are stored as 2 digit zips because leading zero is missing. I want to add the missing leading zeros for 2 digit zips.

I tried the query below but got a lot of errors and the result was not accurate. Should it be written with an if statement checking if the length is 2 characters then concatenate with 0? Or some other way?

This is what I tried:

Update TABLE set ZIPS = string(3 - len(ZIPS),"0")

I had the following error message:

MS Access didn't update 1930 fields due to a type conversion failure, 0 records due to key violations, 0 records due to lock violations, o records due to validation rule violations.

Update TABLE set ZIPS = ZIPS & "0" WHERE Len(ZIPS) = 2. backup your current data first and run a SELECT statement first to check whether expected data is returned: SELECT ZIPS FROM TABLE WHERE Len(ZIPS) = 2 — Rene
Actually, ZIPS = "0" & ZIPS WHERE Len(ZIPS)=2. If you want to use String(): ZIPS = String(3 - len(ZIPS),"0") & ZIPS. — June7
@June, correct. I mixed up trailing and leading. Don't understand why this question's been closed. Seems like Stackoverflow is getting more "rude" again. — Rene
I understand why it's closed, it doesn't provide a minimal reproducible example. For type conversion failures, one should provide the table definition, as well as if there are null values. Also, the query provided will set the field equal to only zeroes instead of adding zeroes which is weird — Erik A

forpas forpas · Accepted Answer · 2019-12-25T11:33:22

The simplest way to do this is with the function FORMAT():

update tablename
set zips = format(zips, '000')
where len(zips) < 3

For these values:

the result will be:

Adding a leading zero via update query in Access table

2 Answers