Formatting - Splitting Text

T

Tatakau

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
 
6

'69 Camaro

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.


Tatakau said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top