Solved

Multiple formats in the same field

Posted on 2013-11-09
7
213 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 11

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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

Author Closing Comment

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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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 …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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 …

830 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