Using an update query with multiple criteria

Question

I have a column of ZIP codes, some of which I need to add a leading zero to in order to make it five digits and some of which are blank and need to be populated with five zeroes. How do I do both in one update query?

Thanks!

HansUp · Accepted Answer · 2013-04-25 18:31:27Z

2

Concatenate 5 zeroes to the start of the Zip code value, and take the right-most 5 characters from that combined string.

UPDATE YourTable
SET zip_code = Right('00000' & zip_code, 5)
WHERE Len(Trim(zip_code & '')) < 5;

I assumed the zip_code field is text type because storing leading zeroes doesn't make sense for numeric data. If the field is numeric, you could just use a Format() expression to display the leading zeroes.

The WHERE clause limits the UPDATE to only those rows where zip_code is Null or less than 5 characters.

edited Apr 25, 2013 at 18:31

answered Apr 25, 2013 at 18:26

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Katheryn Overman Over a year ago

Thanks for the quick response. An additional issue I neglected to mention is that some of the fields have the ZIP +4, which I want to retain. I just need all of the ZIPs to have at least five digits.

HansUp Over a year ago

The WHERE clause excludes rows whose zip_code values include 5 or more characters. So your stored ZIP+4 values will not be changed. Is there a problem I'm not seeing?

Collectives™ on Stack Overflow

Using an update query with multiple criteria

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related