zip code fix 1

[ODBCERRORKING]

i have a field in access that is a zip code field i dont know if someone has dealt with this before but upon import it drops the zero at the beginging of the zip all iwant to do is an update qry ti say! if zip_code holds 4 characters put a zero in front
can anone help!

 

[VicM]

PFUNK:

Define the zip code field in your table as 'text' not 'number'. That way whatever you type in will be stored, even if someone types in the zip+4; e.g. 00000-0000.

The field in the table will show the whole thing including the dashes. Of course you can use and Input mask to show the dash in the box as someone is entering data, and the dash won't be input into the table.

And then you could use the Format property of the box to show as the zip+4, whatever text number is stored in your table without the dash.

HTH,

Vic

 

[ODBCERRORKING]

have already defined as text but there are still 4 digit zips all in need to do is some sort of update qry where if there are only 4 charecters put a 0 in front no?

 

[VicM]

PFUNK:

Did you define the table zip field before or after you entered zip data into the table?

If it was defined before any data was entered, then the entire 5 digit would have been stored. But if it was initially defined as text, then redefined as number and redefined again as text, you would have lost the leading zeros.

If this is a one-time correction, I've found that a better way to handle this kind of update is to use the services of Excel, instead of writing code that will only be used once.

Open a blank Excel spredsheet, and your table in the database. Format the first two columns in Excel to Text.
Then in your db table select the entire zip column and paste it into the first spreadsheet column. It will also put the column header in the first row.

In the first row of the second spreadsheet column thype the following equation:

=If(Len(Trim(A2))=4,"0" & A2,A2)

Copy this equation all the way down to the end of your first column's data. Select the entire 2nd column and copy it. Then do a Paste Special ... Values right over itself.

Now copy the second column (be sure the first row has the identical table column name) and select the zip column in your db table and paste. Access will ask you to confirm the paste. Check that the number of records you are pasting is the same as the number of records in the table.

If you make a mistake, you still have the original data in the first column of the spreadsheet. (It might also be a good idea to save the spreadsheet after you've imported the zip code column.)

HTH,

Vic

 

[mikevh]

Assuming your zipcodes are 5 positions long:

UPDATE YourTable
SET ZipCode = Format(ZipCode, "00000"

This will give you 5-position zero-filled zips.
(Of course adjust to your table name and actual name
of the zipcode field.)