update query to modify zip code 2

[waymond]

I have a table called company that has mail zip and street zip also I have city for both. The mail zip import is messed up I need to update the mail zip if the city matches for both zip with the first 5 numbers from the street zip if the street zip is more than 4. The problem when they downloaded alot of zips from the source only have 4 and should atleast be 5 most of the street zips are 5 so I Need to make sure the city and state are the same than if the street zip is more than four update the mail zip with the first 5 numbers from the street zip

 

[GingerR]

What have you tried so far?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[waymond]

I have tried update query and do not know how to do it. Really I just need for the most part to update the query by putting zero in the begining for example if the zip is 1790 I need to update it to 01790 I can not figure out how

 

[PHV]

if the zip is 1790 I need to update it to 01790
UPDATE yourTable SET zip = '0' & zip WHERE zip Like '####'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

I would assume that the reason the leading 0 is being dropped is because you have defined ZIPCODE as a numeric field. My standard rule of thumb is, if you are going to perform a calculation its a number, anything else is a string. The reason, because a numeric field can't hold a leading 0! How often do you add up your zipcodes or SSN?

You might just need to use the FORMAT command in your query to get the results you need:

Format(Zipcode, '00000')

The only other way is to change the table to store the zipcode as a string and then perform the update to add a leading 0.

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[willir]

Bang on Leslie!

 

[lespaul]

welcome back Richard! haven't "seen" you around lately!

Thanks for the star!

les

 

[willir]

Yea, to say I have been busy for the last few months is an understatement. Glad to see you and PHV and the others fielding all the questions.