How do I add a leading zero to a zip code field that lost it upon.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Novice Access User in NJ - 30 Sep 2004 17:55 GMT

How do I add a leading zero back to a Zip Code field that apparently lost the
leading zero upon importing from original Excel file?

Reply to this Message

tina - 30 Sep 2004 18:17 GMT

check the field's Data Type. it probably imported as a Number data type, in
which case you'll probably want to change it back to Text. then you can use
an update query to add back the missing zeros, with an IIf() function, such
as

IIf(Len(ZipCode) = 4, "0"+ZipCode, ZipCode)

changing, ZipCode to the actual name of your zip code field.

hth

> How do I add a leading zero back to a Zip Code field that apparently lost the
> leading zero upon importing from original Excel file?

Reply to this Message

Douglas J. Steele - 30 Sep 2004 18:41 GMT

Safer might be

Right$("00000" & ZipCode, 5)

just in case there are zip codes that start 00 or 000. (Sorry: I'm Canadian,
and don't know what's valid for zip codes)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

> check the field's Data Type. it probably imported as a Number data type, in
> which case you'll probably want to change it back to Text. then you can use
[quoted text clipped - 10 lines]
> the
> > leading zero upon importing from original Excel file?

Reply to this Message

tina - 30 Sep 2004 19:11 GMT

you're right, Doug, that's much easier! (i always seem to do things the
hard way! <g>)

> Safer might be
>
[quoted text clipped - 26 lines]
> > the
> > > leading zero upon importing from original Excel file?

Reply to this Message