Change phone format

Hi there, I have a phone field with the data type as text.  How do I delete the "(" & ")" in the field?  For example, the phone number is (603) 208-1101 and I need it to be 6032081101.

Thanks
LVL 2
PeppermintAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashen_ShugarCommented:

If you want to see the field as (603) 208-1101 but have it stored internally as 6032081101 then you need to make a small change to the Input Mask.

Design the table and select the Phone Number field. In the input mask which will be like this:

\(9999") "00090009;0;_

change to:

\(9999") "00090009;1;_

The change of 0 to 1 means the literal characters are displayed but not stored in the table. This means the () show in the table, but if the input mask is not present, i.e. in a form text box with no mask, you will only see the numbers.

You could of course remove the input mask but that is not as good.



Stephen

0
PeppermintAuthor Commented:
I need to store the value as 6032081101.  How do I do that?
0
Ashen_ShugarCommented:
If you change the 0 to a 1 the data really is stored as 6032081101, it just APPEARS as (603) 208-1101.

To prove it, enter a phone number using the input mask (with the 1 instead of the 0) and then go into the design of the table and remove the input mask. You will see that only the numbers are stored. If you add the input mask back in you will see the phone number displayed with the () again. The input maks with a 1 shows only cosmetic changes to your data, but does not effect how it is stored.

If you have existing phone numbers with () you want to remove do this.

Open the table and click in the phone number field, Select Replace from the Edit menu and in Find What type (

Leave Replace with blank, make sure Look In says the name of your Phone field and change Match to any part of field. Now click on replace all.

Do the same for ) and -

If you only want a plain text field remove the input mask completely from the field and just type the numbers.

If it is still not clear let us know.




Stephen
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PeppermintAuthor Commented:
The input masks works like a charm for NEW numbers but I need to change existing phone numbers stored in that field.  Please note that the data type is currently text.

I'm assuming I need to do an update query but can't seem to get trim, lens, right in the correct combination.
0
joekendallCommented:
Are they all in the consistent format of (603) 208-1101? If so, you can do this in an update query.

UPDATE MyTable
SET Phone = Mid$(Phone,2,3) & Mid$(Phone,7,3) & Mid$(Phone,11,4)
WHERE Instr(1,Phone,"(")>0

Joe
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeppermintAuthor Commented:
Hi Joe,

The query works great!!  Anyway, got another field where the phone is in this (603)208-1101.  I just can't seem to get the query modified correctly, could you helpout?

Thanks!
0
joekendallCommented:
UPDATE MyTable
SET Phone = Mid$(Phone,2,3) & Mid$(Phone,6,3) & Mid$(Phone,11,4)
WHERE Instr(1,Phone,"(")>0

Joe
0
PeppermintAuthor Commented:
oops, dropping the last digit. any recommendations!!
0
joekendallCommented:
The last digit from where? If it is the last digit in the phone, change it this way.

UPDATE MyTable
SET Phone = Mid$(Phone,2,3) & Mid$(Phone,6,3) & Mid$(Phone,11)
WHERE Instr(1,Phone,"(")>0

Joe
0
PeppermintAuthor Commented:
Still happening.  The phone number ends up 603208101 if the existing phone is (603)208-1101.  

I'll post extra pts if you want them. Thanks again for your help!
0
Ashen_ShugarCommented:


The last part should be 10 not 11.




Stephen
0
PeppermintAuthor Commented:
As you can see, the last digit 1 is getting trimmed off.  Please note that I am using the example provided in the testing.  
0
PeppermintAuthor Commented:
Got it!  THanks!!!
0
PeppermintAuthor Commented:
Help, can you help me with another phone #, 208-1101 needs to be converted to 2081101.

0
PeppermintAuthor Commented:
Hi Joe, found the answer and below is some stuff that may be helpful from Microsoft's website, from article Q210537.

 common use for the RemoveAlphas() function is to remove the parentheses, dashes, and spaces from a telephone number or a social security number field. For example, the following strings contain parentheses, dashes, and spaces:
"(206) 635-7050"
"206-635-7050"
"535-87-4529"


After you run the RemoveAlphas() function, these strings will look as follows:
"2066357050"
"2066357050"
"535874529"

1 Create a new module.
2 Type or copy the following function in the Module window:

Function RemoveAlphas (ByVal AlphaNum as Variant)

   Dim Clean As String
   Dim Pos, A_Char$

   Pos = 1
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ >= "0" And A_Char$ <= "9" Then
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   RemoveAlphas = Clean$

End Function
                              
Save the module with any unique name.

How to Use the RemoveAlphas() Function in an Update Query
1 Create a new query based on the table with the phone number field.
2 Place the phone number field in the first column of the query grid.
3 On the Query menu, click Update.
4 In the Update To row, enter the following for a field named Phone: RemoveAlphas([Phone])

5 Run the query.



0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.