Updating Zip Code field (1 Viewer)

J

jrubins

Registered User.

Local time

Today, 02:23

Joined

Jul 13, 2009

Messages

12

• Aug 10, 2009

• #1

I have a bunch of 5 digit zip codes that were imported into Access and the zip codes that had "0" as the first digit lost that "0" and just became 4 digit zip codes.

I changed the zip code field in the table to a text field with an input mask of "00000/-9999;0;_" but the zip codes that lost their first digit "0"s were unchanged.

Is there some way to run a query to add a "0" to the beginning of any four digit zip codes? Any help would be greatly appreciated.

 

Kryst51

Singin' in the Hou. Rain

Local time

Today, 04:23

Joined

Jun 29, 2009

Messages

1,898

• Aug 10, 2009

• #2

Check out this.

http://www.access-programmers.co.uk/forums/showthread.php?t=177271&highlight=leadinghttp://www.access-programmers.co.uk/forums/showthread.php?t=177271&highlight=leading

 

J

jrubins

Registered User.

Local time

Today, 02:23

Joined

Jul 13, 2009

Messages

12

• Aug 10, 2009

• #3

The problem is I can't re-import the data because I don't have access to the zip code file anymore. Also, I've made changes within my database and if I re-import the zip code file, I'll lose all those changes I've made.

Is there some way to add that first "0" to the data I already have imported without having to re-import (some Visual Basic Code or a kind of update query??)?

 

boblarson

Smeghead

Local time

Today, 02:23

Joined

Jan 12, 2001

Messages

32,059

• Aug 10, 2009

• #4

You could use an Update query like this:

Code:Copy to clipboard

UPDATE YourTableNameHere SET ourTableNameHere.ZipCode = "0" & [ZipCode]
WHERE Len([ZipCode])<5;

 

J

jrubins

Registered User.

Local time

Today, 02:23

Joined

Jul 13, 2009

Messages

12

• Aug 10, 2009

• #5

Thanks a lot! That worked perfectly.

 

boblarson

Smeghead

Local time

Today, 02:23

Joined

Jan 12, 2001

Messages

32,059

• Aug 10, 2009

• #6

Great, glad to hear.