Leading zero in Zip Code

[Tlm]

Hello All,

I'm importing Contact data from a Excel spreadsheet into an Access table.
No problem there. However, here in Massachusetts our zip codes start with 0
and the spreadsheet omits it. I'm trying to use an Update Query to insert
the leading 0 in the zip code field (text).

I've tried the following, but it only inserts a 0 in all the records:
Expr1: IIf(Left([BusinessPostalCode],1)<>"0",0 &
[BusinessPostalCode],[BusinessPostalCode])

I'm stumped. How do you do this?????

Thanks!!!!!!

Tom

 

[R. Hicks]

This should be in the "Update To" section of the zipcode field.

IIf(Left([BusinessPostalCode],1)<>"0","0" &
[BusinessPostalCode],[BusinessPostalCode])

This is assuming that "BusinessPostalCode" is a text datatype.

RD

 

[Sue Harsevoort]

If the spreadsheet is omitting leading zeros then
Left([BusinessPostalCode],1)<>"0" is always going to be true so this will
always add a 0 to the front of the zip code like you said. You need to look
at the length of BusinessPostalCode and add the 0 if the length was only 4
(I don't know if zip codes can ever start with more than 1 0, but you may
want to check for any length less than 5 and add the appropriate number of
0's)

Sue

 

[John Vinson]

I'm importing Contact data from a Excel spreadsheet into an Access table.
No problem there. However, here in Massachusetts our zip codes start with 0
and the spreadsheet omits it. I'm trying to use an Update Query to insert
the leading 0 in the zip code field (text).

I've tried the following, but it only inserts a 0 in all the records:
Expr1: IIf(Left([BusinessPostalCode],1)<>"0",0 &
[BusinessPostalCode],[BusinessPostalCode])

One sneaky way to do this is to run an Update query updating Zip to

Right("00000" & [Zip], 5)

This will take 83660 to 0000083660, and trim it to 83660; and will
take 2138 to 000002138 and trim it to 02138.

 

[Tlm]

Sue,

Excellent! Many thanks!!!


Tom

If the spreadsheet is omitting leading zeros then
Left([BusinessPostalCode],1)<>"0" is always going to be true so this will
always add a 0 to the front of the zip code like you said. You need to look
at the length of BusinessPostalCode and add the 0 if the length was only 4
(I don't know if zip codes can ever start with more than 1 0, but you may
want to check for any length less than 5 and add the appropriate number of
0's)

Sue

Hello All,

I'm importing Contact data from a Excel spreadsheet into an Access table.
No problem there. However, here in Massachusetts our zip codes start

with

0
and the spreadsheet omits it. I'm trying to use an Update Query to insert
the leading 0 in the zip code field (text).

I've tried the following, but it only inserts a 0 in all the records:
Expr1: IIf(Left([BusinessPostalCode],1)<>"0",0 &
[BusinessPostalCode],[BusinessPostalCode])

I'm stumped. How do you do this?????

Thanks!!!!!!

Tom

 

[Tlm]

RDH,

Thanks for your post! That should do the trick.

Thanks again,

Tom

 

[Tlm]

John,

Very clever. Thanks!!!

Tom

I'm importing Contact data from a Excel spreadsheet into an Access table.
No problem there. However, here in Massachusetts our zip codes start

with
0

and the spreadsheet omits it. I'm trying to use an Update Query to insert
the leading 0 in the zip code field (text).

I've tried the following, but it only inserts a 0 in all the records:
Expr1: IIf(Left([BusinessPostalCode],1)<>"0",0 &
[BusinessPostalCode],[BusinessPostalCode])

One sneaky way to do this is to run an Update query updating Zip to

Right("00000" & [Zip], 5)

This will take 83660 to 0000083660, and trim it to 83660; and will
take 2138 to 000002138 and trim it to 02138.