Missing Zeros

[tim]

I receive batch files in the form of csv files and process them in Access.
In the zip code fields that should that start with a zero the zero is always
missing. Is there a way to run a query on the zip code field to check for 5
characters and ad a zero where only 4 are present?

Any help is appreciated

MS Office 2000

Tim

 

[Rick B]

Sure.

First make sure it is a TEXT field, not a NUMBER.

Then build a query with a new column like...

=Len([YourZipFieldNameHere])

in the cirteria under that column put...

=4

Run it to make sure you get what you want.

Change it to an update query and in the UPDATE TO field in the ZIP column
put...

="0"&[YourZipCodeFieldNameHere]


Hope that does the trick for you,

 

[Jeff Boyce]

Tim

I'd guess your Access table has "zipcode" defined as a number data type.
Zip codes are ... "codes", not numbers. You don't add/subtract them.

If I gave you the number 1, or 00001, they're both the same value.

If you need the character "0" as part of your zip codes, use the text data
type.

(And you may want to confirm that the input csv file actually has the
"0"'s -- otherwise, you'll need to both convert to text AND add the "0"'s.)

Good luck

Jeff Boyce
<Access MVP>

 

[tim]

Rick,

That did the trick.

Thanks
Tim

Sure.

First make sure it is a TEXT field, not a NUMBER.

Then build a query with a new column like...

=Len([YourZipFieldNameHere])

in the cirteria under that column put...

=4

Run it to make sure you get what you want.

Change it to an update query and in the UPDATE TO field in the ZIP column
put...

="0"&[YourZipCodeFieldNameHere]


Hope that does the trick for you,

--
Rick B

I receive batch files in the form of csv files and process them in Access.
In the zip code fields that should that start with a zero the zero is always
missing. Is there a way to run a query on the zip code field to check

for

5
characters and ad a zero where only 4 are present?

Any help is appreciated

MS Office 2000

Tim