Multiple formats in the same field

Posted on 2013-11-09
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
  • 3
  • 3
LVL 11

Expert Comment

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?
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now