Zipcode Format

[Full_Williams]

Hi,

I'm trying to display addresses in a mail label report like this:

John Doe
1234 S. Street
San Francisco, CA 22102

My problem is that some addresses within my table have 9 digits and some have only 5. The addresses with 5 digits display correct while the address with 9 digits display as

"221022222" instead of "22102-2222". Without the "-" in there.

I tried formatting the zip field in the query like this:

=([City] & " " & [State_Acronym] & ", " & Format([Zip],"00000-9999;0;_"))

but Access keeps changing it to:

=([City] & " " & [State_Acronym] & ", " & Format([Zip],"00000""-9999"";0;_"))

Access keeps adding extra quotes in there.

Any ideas on how I can get around this problem.

Any help would be appreciated.

Thanks,
Full Williams

 

[Matthew Snook]

Full:

I did not try to recreate your problem, but there are several things to try first.

Is the zip code field text or numeric? A zip code can have some or all numerals for the characters, but should be treated as text (if only because they often have letters!). Once you have the [Zip] in text format, you can use the "Left" and "Right" text functions to redraw your zip codes thus:

=([City] & " " & [State_Acronym] & ", " & Left([Zip], 5) & "-" & Right([Zip], 4))


Hope that helps,

Matt

 

[Full_Williams]

Matt,

Thanks for the quick response.

The zip field is text.

That works for the 9 digit zipcodes, but now the zipcodes that only have 5 digits are showing:

"22102-2102" when it should show "22102"

It's adding the last 4 digits of the zip to the end.

Any other thoughts?

Thanks,
Full Williams

 

[Matthew Snook]

Oops. That was a rather obvious oversight. Perhaps you could use an "iif" statement to put in a blank if the length of the zip is greater than five. So now you have:

=([City] & " " & [State_Acronym] & ", " & Left([Zip], 5) & "-" & IIf(Len([Zip])>5,Right([Zip],4),""))


Matt

 

[Full_Williams]

Works Great!!

Thanks for your help Matt. That was killing me.

 

[pdx_man]

FYI

You are trying to use Input Mask codes for the Format function. Doesn't quite work that way Under Access Help, look up the Format Function. Then go to See Also and select User-Defined String Formats.


=([City] & " " & [State_Acronym] & ", " & Format([Zip],"!@@@@@-@@@@"))

 

[Matthew Snook]

=([City] & " " & [State_Acronym] & ", " & Format([Zip],"!@@@@@-@@@@"))
Great stuff!

So now if you combine that with the "Iif" statement to get rid of the "-" when you have a short zip, you've got what you were trying for.

Matt




P.S.
Now to handle those pesky Canadian six digit mixed alphanumeric zips...