Solved

Change phone format

Posted on 2003-10-24
15
422 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:Peppermint
  • 9
  • 3
  • 3
15 Comments
 
LVL 4

Expert Comment

by:Ashen_Shugar
Comment Utility

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
 
LVL 2

Author Comment

by:Peppermint
Comment Utility
I need to store the value as 6032081101.  How do I do that?
0
 
LVL 4

Expert Comment

by:Ashen_Shugar
Comment Utility
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
 
LVL 2

Author Comment

by:Peppermint
Comment Utility
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
 
LVL 11

Accepted Solution

by:
joekendall earned 500 total points
Comment Utility
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
 
LVL 2

Author Comment

by:Peppermint
Comment Utility
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
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
UPDATE MyTable
SET Phone = Mid$(Phone,2,3) & Mid$(Phone,6,3) & Mid$(Phone,11,4)
WHERE Instr(1,Phone,"(")>0

Joe
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 2

Author Comment

by:Peppermint
Comment Utility
oops, dropping the last digit. any recommendations!!
0
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
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
 
LVL 2

Author Comment

by:Peppermint
Comment Utility
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
 
LVL 4

Expert Comment

by:Ashen_Shugar
Comment Utility


The last part should be 10 not 11.




Stephen
0
 
LVL 2

Author Comment

by:Peppermint
Comment Utility
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
 
LVL 2

Author Comment

by:Peppermint
Comment Utility
Got it!  THanks!!!
0
 
LVL 2

Author Comment

by:Peppermint
Comment Utility
Help, can you help me with another phone #, 208-1101 needs to be converted to 2081101.

0
 
LVL 2

Author Comment

by:Peppermint
Comment Utility
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

13 Experts available now in Live!

Get 1:1 Help Now