469,580 Members | 1,865 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,580 developers. It's quick & easy.

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
Nov 12 '05 #1
5 13393
On Tue, 18 Nov 2003 13:42:07 GMT, "Tlm" <t.*******@comcast.net> wrote:

Format(BPC,"00000")
-Tom.


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


Nov 12 '05 #2
Tlm
Tom,

Much thanks!!!
Obviously, I forgot the most important principle; 'Keep it simple'.

Thanks again!!!!

Tom
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:bb********************************@4ax.com...
On Tue, 18 Nov 2003 13:42:07 GMT, "Tlm" <t.*******@comcast.net> wrote:

Format(BPC,"00000")
-Tom.


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 0and the spreadsheet omits it. I'm trying to use an Update Query to insertthe 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

Nov 12 '05 #3
"Tlm" <t.*******@comcast.net> wrote:
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 can't see anything wrong with what you've done Tom, but the result *will*
give a leading zero in *all* records. Isn't that what you want?

Regards,
Keith.
www.keithwilby.org.uk
Nov 12 '05 #4
Tlm wrote:

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

Tom,
Tom van Stiphout gave you an easy solution using the Format property,
but to answer your question of to how to write an Update query to do
this....

1) The Zip field must be a Text datatype, not a number datatype.
Otherwise it will always drop the preceeding zero.

2) For the Update query write:
Update YourTable Set YourTable.BusinessPostalCode = "0" &
[BusinessPostalCode] Where len([BusinessPostalCode]) < 5;

Or if, as in your example, you simply wish to display this in a Select
query:
Exp1:IIf(Len([BusinessPostalCode])<5,"0" &
[BusinessPostalCode],[BusinessPostalCode])

--
Fred
Please reply only to this newsgroup.
I do not reply to personal email.
Nov 12 '05 #5
Tlm
Fred,

Worked like a charm. Thanks a lot!!!

Tom

"Fredg" <fg******@example.invalid> wrote in message
news:%I***********************@bgtnsc04-news.ops.worldnet.att.net...
Tlm wrote:

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

Tom,
Tom van Stiphout gave you an easy solution using the Format property,
but to answer your question of to how to write an Update query to do
this....

1) The Zip field must be a Text datatype, not a number datatype.
Otherwise it will always drop the preceeding zero.

2) For the Update query write:
Update YourTable Set YourTable.BusinessPostalCode = "0" &
[BusinessPostalCode] Where len([BusinessPostalCode]) < 5;

Or if, as in your example, you simply wish to display this in a Select
query:
Exp1:IIf(Len([BusinessPostalCode])<5,"0" &
[BusinessPostalCode],[BusinessPostalCode])

--
Fred
Please reply only to this newsgroup.
I do not reply to personal email.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by GarryJones | last post: by
8 posts views Thread by Andrew Poulos | last post: by
4 posts views Thread by bobm2005 | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.