473,326 Members | 2,192 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 14035
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: yanir | last post by:
Hi I use reponse.contenttype = "application/vnd.ms-excel" So the browser will show the data in excel format, but for some fields I use leading zero's, which truncated by the browser, at this...
2
by: Jeff Lowry | last post by:
I'm pasing a zip code as a prameter to an Access stored procedure. In Access the parameter is a text data type. It works for non-leading zero zip codes but, apparently access (or ASP) is...
7
by: david | last post by:
Hi, I have 2 text boxes on an ASP form. A user enters a Serial Number in TB1 such as 0105123456, presses tab to move to TB2, TB2 then displays the value of TB1 after a calculation has been...
1
by: Joshua Ammann | last post by:
Hello, I'm trying to export a query containing contact information, including a field. Some zip codes have one or two leading zeros, for example, San Juan, PR (00927) and Springfield, MA...
5
by: GarryJones | last post by:
I have code numbers in 2 fields from a table which correspond to month and date. (Month, Code number) Field name = ml_mna 1 2 3 etc up to 12 (Data is entered without a leading zero)
24
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - How can I see in javascript if a web browser accepts cookies?...
8
by: Andrew Poulos | last post by:
In my limited testing with FF 2, IE 6 and Opera 9 when I divided a positive integer, that is less than 100, by 100 I get a leading zero in front of the decimal point. For example 80/100 gives...
4
by: bobm2005 | last post by:
Whatever format I try in Printf, an 'E' format number nearly always has a leading non-zero:- 1.2345E7 -9.3456E8 etc. Is it possible to force it (printf) always to have leading zero? ...
1
by: lornab | last post by:
Good Afternoon I need a column to show in a report as 10 digits long by adding zero's to the left of the result. My select statement is like this: SELECT a.client, a.apar_id FROM...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.