Formatting - Splitting Text

[Guest]

There are several fields stored in a field for guest information that I need
to format. Specifically, Zip Code and Phone Number.

Both fields are stored as text, and without any symbols. For example,
someone with a zip code of 12345-1234 is stored as "123451234". Another
example, and probably easier to understand: someone with a phone number of
(555) 123-4567 would have their number stored as "5551234567".

I am trying to print these in a merged Word document by linking to a query.
However, the numbers are of course printing as: 5551234567, instead of the
desired: (555) 123-4567.

I was able to format the dates and times as I wanted them... for example:
TourDay: Format([tourdate],"dddd"", ""mmmm d"", ""yyyy")
CheckOut: Format([CheckOutTime],"hh:nn AM/PM")

I don't see how I can do anything that with a string though. I've already
searched through the help files and the forum... does anyone know of a
function that I can use to have the query produce hits in the correct format?

Thanks a million!

Nick

 

[Guest]

Hi, Nick.

does anyone know of a
function that I can use to have the query produce hits in the correct format?

Use a user-defined format. Try:

Zip: Format(ZipCode, "#####-####")
Phone: Format(PhoneNum, "(###) ###-####")

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.

There are several fields stored in a field for guest information that I need
to format. Specifically, Zip Code and Phone Number.

Both fields are stored as text, and without any symbols. For example,
someone with a zip code of 12345-1234 is stored as "123451234". Another
example, and probably easier to understand: someone with a phone number of
(555) 123-4567 would have their number stored as "5551234567".

I am trying to print these in a merged Word document by linking to a query.
However, the numbers are of course printing as: 5551234567, instead of the
desired: (555) 123-4567.

I was able to format the dates and times as I wanted them... for example:
TourDay: Format([tourdate],"dddd"", ""mmmm d"", ""yyyy")
CheckOut: Format([CheckOutTime],"hh:nn AM/PM")

I don't see how I can do anything that with a string though. I've already
searched through the help files and the forum... does anyone know of a
function that I can use to have the query produce hits in the correct format?

Thanks a million!

Nick

 

[John Vinson]

I don't see how I can do anything that with a string though.

It's a bit obscure but doable:

ShowZip: Format([ZIP], "@@@@@-@@@@")


John W. Vinson[MVP]