Phone Conversion

[hycho]

Hi all,

I have a question about changing the formats of phone numbers. In my current database, I have these type of numbers:

222-222-2222
(222)-222-2222
(222-222-2222
222-222-2222
etc.

I would like to run a query to change the format of the above phone numbers to just having ten digits with no spaces or symbols, such as example below:

2222222222

Any suggestions would be appreciated. Thanks in advance.

 

[boblarson]

You could use the REPLACE function in an Update Query.

 

[hycho]

Thanks for the help, but I should clarify the issue more.

I have a list of phone numbers (over 10,000 records) in my database where the format varies quite a bit. I would like to change the format for these 10,000 records, so that they will look like "0123456789" (i.e. 10 digits, no space and symbols).

Is there a way for me to get the output I desire, besides using find and replace? I use to do this on Excel by finding "(" and replacing with "", then reiterate finding ")" and replacing with "".

Just wondering if it there is a more efficient of way doing this.

 

[boblarson]

You could use this method (copy this into a standard module and then just call it):

Function ChangeToNumbers(strTable As String, strField As String)
    Dim rst    As DAO.Recordset
    Dim strHold As String
    Dim i      As Integer
 
    Set rst = CurrentDb.OpenRecordset("Select * From [" & strTable & "]")
 
    With rst
 
        Do Until rst.EOF
 
            If Len(.Fields(strField).Value & vbNullString) > 0 Then
 
                For i = 1 To Len(.Fields(strField).Value)
 
                    If IsNumeric(Mid(.Fields(strField).Value, i, 1)) Then
 
                        strHold = strHold & Mid(.Fields(strField).Value, i, 1)
 
                    End If
 
                Next
 
                .Edit
 
                .Fields(strField).Value = strHold
 
                .Update
 
                strHold = vbNullString
 
                rst.MoveNext
 
            End If
 
        Loop
 
    End With
 
End Function

Then you can simply come to the VBA Window and call it to update:

ChangeToNumbers "PutYourTableNameHere", "PutYourFieldNameToUpdateHere"

But make a copy of the db first - just in case something doesn't work right.

 

[hycho]

Thanks for the quick reply.

I am surprised I actually understand the code since I did take an intro class to C++ in college. However, I don't know how to execute this code from the VBA window screen. I have changed the parameters in the "ChangeToNumber()" function in respect to my database.

After changing the parameters, I tried hitting the run button on top of the screen and I get a prompt to create a Macro. Also, I have tried running a query to call this function, but I am unsure of to enter the table parameter for this function...

In short, can you tell me how I can run this function?

Thanks for the help again.

 

[boblarson]

You shouldn't have changed any of the parameters. The function would remain exactly as it is. You would simply type this into your Immediate Window:

ChangeToNumbers "PutYourTableNameHere", "PutYourFieldNameToUpdateHere"

And changing the parts I have as PutYour... and then hit enter.

 

[hycho]

Thanks again, but I am not exactly sure if I am doing this correctly. Here's a list of steps I've taken.

1. Open a new query, with table name "NewKirk"
2. Type in "changeToNumbers(x,y)" in a new field below.
3. I ran the query... I get the prompt "enter parameter value x"... i type in "NewKirk"
4. Get a second prompt "enter parameter value y"... i type in "phone"
5. I then get the prompt in the VB window: "compile error: user-defined type not defined."

I hope this helps clarify what I am doing and my VBA skill level.

Thanks again.

 

[boblarson]

No, you don't open a query.

1. You paste the function into a new standard module.
2. You stay in the VBA Window.
3. You go to the IMMEDIATE WINDOW and type in the

ChangeToNumbers "NewKirk", "phone"

4. Hit Enter Key.

But BEFORE you do that you need to make sure that under TOOLS > REFERENCES (in the VBA Window) you have a DAO reference or, if on 2007/2010 you have the Microsoft Office Access Database Engine reference checked.

 

[hycho]

Thanks again.

When you say "immediate window," do you mean the same window as where I copied and paste the code?

If so, I typed in:

ChangeToNumbers "NewKirk", "phone"

And nothing happened. Also, I went to tools-->references--> and checked off on "Microsoft DAO 3.6 Object Library". I hope that's what you meant by DAO reference. FYI, I have MS ACCESS 2000 version.

Thanks again.

 

[boblarson]

No, I don't mean the same window where you pasted the code. I mean this:

 

[hycho]

Thanks for the tip. I had to open the "immediate window" myself, since it didn't open automatically.

The code takes a while to run, so if there is any tip on how to speed up the process, that would be greatly appreciated.

Thanks again.

 

[Beetle]

This is the type of process you should really only need to run once. If you've done that, and it worked, then from that point you should correct the data entry process to prevent the unwanted characters from being written to the table in the first place.

Are you saying you are going to want to run this repeatedly? If so, why?

 

[hycho]

I don't have production access to the database. So, I can't make change to the actual table, but can only make changes to the copy version of the table. So, I will need to run this code every week or so, since the actual table is live and gets updated everyday.

So, if you can think of a more efficient way, please let me know. Thanks.