Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Multiple formats in the same field

Posted on 2013-11-09
Medium Priority
Last Modified: 2013-11-18
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.
Question by:lheroux
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 12

Expert Comment

by:Gregory Miller
ID: 39636102
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.
LVL 29

Expert Comment

ID: 39636166
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.


Author Comment

ID: 39636192
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?
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 29

Expert Comment

ID: 39636213
I'm assuming you didn't see my post showing the Update query.

Author Comment

ID: 39636232
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.
LVL 29

Accepted Solution

IrogSinta earned 2000 total points
ID: 39636240
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.

Author Closing Comment

ID: 39658276
Thanks very much. The syntax error was mine, not yours.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question