Displaying a Zip Code (5 or more digits)

[Guest]

I have a query that displays guest zip codes, which are stored as text
strings. I would like to add a dash between the 5th and 6th digit if the zip
code is longer than 5 digits (eg, 12345 displays as 12345, and 123456789
displays as 12345-6789). I don't quite know how to code this though... does
anyone know of a command that I could use to do this?

Thanks!

Nick

 

[Guest]

In the column in your query where you have the zip code, change the field row
to

Zip: IIf(Len([ZipCode])>5,Left([ZipCode],5) & "-" &
Mid([ZipCode],6),[ZipCode])

 

[Chaim]

Left(zipcode, 5) & IIF (len(zipcode) > 5, "-" & mid(zipcode, 6), "")

If you want to insure that it is only zip+4- a guaranteed nine digits- you
can replace the '>5' with '= 9'. The whole point of the IIF is to make sure
that there is something after character 5; it avoids an orphaned '-' at the
end.

 

[Guest]

Hi.

I would like to add a dash between the 5th and 6th digit if the zip
code is longer than 5 digits

If you want to make this permanent, then use an UPDATE query with the
Format( ) function. For example:

UPDATE tblZipCodes
SET FullZip = IIF((Len(ZipCode) > 5), Format(ZipCode, "#####-####"), ZipCode)

If you just want it for display purposes, then try:

SELECT IIF((Len(ZipCode) > 5), Format(ZipCode, "#####-####")) AS Zip
FROM tblZipCodes;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

It seems like using the "#" symbols doesn't quite work when typing them into
query fields - it reduces a string of "#######" into just a single "#", no
matter how many there are. Maybe the wrong symbol for Access... however, it
seems to work fine with SQL! (which is a little more in-depth than I'd
really like to get)

Thanks!

Nick

 

['69 Camaro]

Hi, Nick.

It seems like using the "#" symbols doesn't quite work when typing them
into
query fields

The QBE grid is a great tool for getting people started in learning to write
queries or for automating simple queries, but there are so many limitations
with the GUI that for moderately complex queries and the more advanced
queries, SQL is the only way to get the correct data output. I recommend
that you learn SQL if you are going to be dealing with Access or any other
relational database as a major part of your job.

Thanks!

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.