CODEWITHSUNDEEP
sqlms-access
Tyler1979
Tyler1979

Reputation: 9

Adding a leading zero via update query in Access table

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.

Upvotes: 0

Views: 1779

Answers (2)

forpas
forpas

Reputation: 164099

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:

zips
1
15
13
100
99
8

the result will be:

zips
001
015
013
100
099
008

Upvotes: 2

Lee Mac
Lee Mac

Reputation: 16015

There are many ways to achieve this, I might suggest:

update YourTable set zips = "0" & zips where zips like "??"

Alternatively, the following is useful if you want to output 3 digits without updating the stored values:

select right("000" & zips, 3) from YourTable

Upvotes: 0

Related Questions