Multiple formats in the same field

An inherited Access db has Home phone, Cell phone, and Business phone fields. All fields have  phone # as (xxx) xxx-xxxx or xxxxxxxxxx. Please tell me how to convert xxxxxxxxxx to (xxx) xxx-xxxx stored, visible, and able to be merged into a Word document. I would also like to know how to input new data so that these fields remain consistent. Do I use formatting or input mask? Thank you so much.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

IrogSintaConnect With a Mentor Commented:
All you need to do is create a query.  In the QBE's design mode you would select SQL in the Ribbon and paste the code I posted.SQL viewThen just click on RUN (the second icon on the ribbon bar).  You would need to do this for each of your fields.
Gregory MillerGeneral ManagerCommented:
If the number is stored as xxxxxxxxxx in the data table, that is you best option. This way you can control your formatting at the time you want to look at the number in a report, form, etc.

Ideally the number should be broken into three fields areacode [AC], exchange [EX], number[NB]. This way you have the best control over the output with the least amount of work. Output would be "("&[AC]&") "&[EX]&"-"&[NB]. Pretty simple. This also allows you to do index searches lightning fast on any portion of the phone number is you have that need.
I agree with Technodweeb about storing the number without any non-numeric characters though I don't think it is a good idea to break it up into 3 fields each.  You'd end up having 9 fields for 3 phone numbers and every time you had to add phone numbers to your form or report, you'd need to add all 3 fields for each one.  

So in your case, you would to run some code to remove the non-numeric characters in each field.  You then can display the numbers properly using the Format function like this:
= Format([CellNum],"(###) ###-####")
To update your fields run a query for each one of your fields simliar to this:
UPDATE TableName SET [HomePhone] = Replace(Replace(Replace(Replace([HomePhone],"(",""),")",""),"-","")," ","");

Open in new window

The above query replaces open and close parenthesis, dashes, and spaces within the HomePhone field with null strings.  Of course you have to modify it to include your actual field and table names.

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

lherouxAuthor Commented:
The number is stored both ways depending on who/when entered. I like what you say about xxxxxxxxxx better than breaking into 3 fields. The numbers are used as personal info, not geographic, etc. How can I convert (xxx) xxx-xxxx  to xxxxxxxxxx without retyping everything?
I'm assuming you didn't see my post showing the Update query.
lherouxAuthor Commented:
I saw it and am trying to use it when I get the syntax error. It's been soooo long since I've written code that I don't see anything wrong.
lherouxAuthor Commented:
Thanks very much. The syntax error was mine, not yours.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.