Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Multiple formats in the same field

Posted on 2013-11-09
7
Medium Priority
?
221 Views
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.
0
Comment
Question by:lheroux
  • 3
  • 3
7 Comments
 
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
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.

Ron
0
 

Author Comment

by:lheroux
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?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 29

Expert Comment

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

Author Comment

by:lheroux
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.
0
 
LVL 29

Accepted Solution

by:
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.
0
 

Author Closing Comment

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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

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…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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 …

876 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