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([BusinessPostalC ode],1)<>"0",0 &
[BusinessPostalC ode],[BusinessPostalC ode])

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

Thanks!!!!!!

Tom

[Tom van Stiphout]

Re: Leading zero in Zip Code

On Tue, 18 Nov 2003 13:42:07 GMT, "Tlm" <t.minehan@comc ast.net> wrote:

Format(BPC,"000 00")
-Tom.

[color=blue]
>
>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([BusinessPostalC ode],1)<>"0",0 &
>[BusinessPostalC ode],[BusinessPostalC ode])
>
>I'm stumped. How do you do this?????
>
>Thanks!!!!!!
>
>Tom
>[/color]

[Tlm]

Re: Leading zero in Zip Code

Tom,

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

Thanks again!!!!

Tom


"Tom van Stiphout" <tom7744@no.spa m.cox.net> wrote in message
news:bb9krvgsbc 88kloo23acjo6r7 enq3bqjjh@4ax.c om...[color=blue]
> On Tue, 18 Nov 2003 13:42:07 GMT, "Tlm" <t.minehan@comc ast.net> wrote:
>
> Format(BPC,"000 00")
> -Tom.
>
>[color=green]
> >
> >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[/color][/color]
with 0[color=blue][color=green]
> >and the spreadsheet omits it. I'm trying to use an Update Query to[/color][/color]
insert[color=blue][color=green]
> >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([BusinessPostalC ode],1)<>"0",0 &
> >[BusinessPostalC ode],[BusinessPostalC ode])
> >
> >I'm stumped. How do you do this?????
> >
> >Thanks!!!!!!
> >
> >Tom
> >[/color]
>[/color]

[Keith Wilby]

Re: Leading zero in Zip Code

"Tlm" <t.minehan@comc ast.net> wrote:
[color=blue]
> I've tried the following, but it only inserts a 0 in all the records:
> Expr1: IIf(Left([BusinessPostalC ode],1)<>"0",0 &
> [BusinessPostalC ode],[BusinessPostalC ode])[/color]

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.

https://www.keithwilby.org.uk

[Fredg]

Re: Leading zero in Zip Code

Tlm wrote:
[color=blue]
>
> 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([BusinessPostalC ode],1)<>"0",0 &
> [BusinessPostalC ode],[BusinessPostalC ode])
>
> I'm stumped. How do you do this?????
>
> Thanks!!!!!!
>
> Tom
>
>[/color]
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.Busin essPostalCode = "0" &
[BusinessPostalC ode] Where len([BusinessPostalC ode]) < 5;

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

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

[Tlm]

Re: Leading zero in Zip Code

Fred,

Worked like a charm. Thanks a lot!!!

Tom

"Fredg" <fgutkind@examp le.invalid> wrote in message
news:%Isub.2834 87$0v4.17848291 @bgtnsc04-news.ops.worldn et.att.net...[color=blue]
> Tlm wrote:
>[color=green]
> >
> > Hello All,
> >
> > I'm importing Contact data from a Excel spreadsheet into an Access[/color][/color]
table.[color=blue][color=green]
> > No problem there. However, here in Massachusetts our zip codes start[/color][/color]
with 0[color=blue][color=green]
> > and the spreadsheet omits it. I'm trying to use an Update Query to[/color][/color]
insert[color=blue][color=green]
> > 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([BusinessPostalC ode],1)<>"0",0 &
> > [BusinessPostalC ode],[BusinessPostalC ode])
> >
> > I'm stumped. How do you do this?????
> >
> > Thanks!!!!!!
> >
> > Tom
> >
> >[/color]
> 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.Busin essPostalCode = "0" &
> [BusinessPostalC ode] Where len([BusinessPostalC ode]) < 5;
>
> Or if, as in your example, you simply wish to display this in a Select
> query:
> Exp1:IIf(Len([BusinessPostalC ode])<5,"0" &
> [BusinessPostalC ode],[BusinessPostalC ode])
>
> --
> Fred
> Please reply only to this newsgroup.
> I do not reply to personal email.[/color]