By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,780 Members | 1,520 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,780 IT Pros & Developers. It's quick & easy.

Leading Zeroes in ZIP Code

P: n/a
Newbie question, here.

I have a database that frequently imports ZIP code data that
frequently needs cleaning:

12365
6487
64684-3543
3213-6546
I.e. missing leading zeroes, five digit vs nine digit ZIPs, etc.

How would I format the "Update To" command to: 1) restore leading
zeroes to four and eight digit zips and 2) standardize all ZIPs to
five digits??

Thanks!
Jul 25 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Fri, 25 Jul 2008 06:41:57 -0700 (PDT), DanCole42
<da*******@gmail.comwrote:

First off you need a text field.
Then make the first pass and strip off anything starting with "-". You
could do that with a combination of Left$ and InStr.
Then make a second pass and add leading zeros. You could do that with
Format or with Len.

Give it a try.

-Tom.
Microsoft Access MVP
>Newbie question, here.

I have a database that frequently imports ZIP code data that
frequently needs cleaning:

12365
6487
64684-3543
3213-6546
I.e. missing leading zeroes, five digit vs nine digit ZIPs, etc.

How would I format the "Update To" command to: 1) restore leading
zeroes to four and eight digit zips and 2) standardize all ZIPs to
five digits??

Thanks!
Jul 25 '08 #2

P: n/a
iif(instr(1,[ZIP],"-")=0,format([ZIP],"00000"),format(left([ZIP],instr(1,[ZI
P],"-")-1),"00000"))

"DanCole42" <da*******@gmail.comwrote in message
news:5d**********************************@k36g2000 pri.googlegroups.com...
Newbie question, here.

I have a database that frequently imports ZIP code data that
frequently needs cleaning:

12365
6487
64684-3543
3213-6546
I.e. missing leading zeroes, five digit vs nine digit ZIPs, etc.

How would I format the "Update To" command to: 1) restore leading
zeroes to four and eight digit zips and 2) standardize all ZIPs to
five digits??

Thanks!

Jul 25 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.