Link to home
Start Free TrialLog in
Avatar of Varda
Varda

asked on

Updating format of a phone no field

I have a database (MS Access 2003) where the user imported from MS Excel 2003 and then updated an existent table in the database. The table has a field with the input mask for a phone number   !\(999") "000\-0000;0;_

The problem I've noticed is that some records are now showing the phone number not in the correct format. They show as parentices without dashes.

How do I update the field to show all records in the correct format? (with parentices and dashes).

Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Consider doing this (after a backup):

- Clean up numbers you got from Excel.  This means, to get rid of all ( or - or any other characters may exist.

- Say your existing field after import is [PhoneNo].  Rename it To [PhoneNoOld]; Go to table design view and copy and past [PhoneNoOld] to rename it as [PhoneNo], save it and run it.  You will see the numbers exists in [PhoneNoOld] but [PhoneNo] is blank.

Be back shortly.

Mike
- Run following query:

CurrentDB.Execute "Update MyTable Set [PhoneNo]= fnPhoneNo([PhoneNoOld])"

After you have following function in a module:

Function fnPhoneNo(strPhone As Stgring) As String

if len(strPhone)<>0 Then
strPhone = Replace(strPhone,"(","")
strPhone = Replace(strPhone,")","")
strPhone = Replace(strPhone,"-","")
strPhone = Replace(strPhone," ","")

' if any other characters exit, use Replace() like shown above to clean them up

fnPhoneNo=Format(strPhone,"0000000000")
' to store missing area codes as 000 543-2351
' how about the extensions?
End Function
Mike
Avatar of Varda
Varda

ASKER

Mike,

Thank you for the prompt response. I tried your first suggestion but I'm not seeing how this resolves the problem. I'm going to try your second suggestion, though I'm not very knowledgable about modules. I'll give it a try now.

Varda
re:> knowledgable about modules

In your database window, click on Module tab, click on new, copy and past what I gave you above there and same the module as any name you choose (doesn't matter).

But, make sure:
                                           v-- is your table name
CurrentDB.Execute "Update MyTable Set [PhoneNo]= fnPhoneNo([PhoneNoOld])"
                                                              ^---- are your field names---^
Do you ask about one question per year?
Avatar of Varda

ASKER

I figured out the module, but can't figure out how to run the query you suggest:
CurrentDB.Execute "Update MyTable Set [PhoneNo]= fnPhoneNo([PhoneNoOld])"

When I start a new query, and go into the SQL view I changed the MyTable to my table's name, and the fields to have the correct names [BusinessPhoneNo] and [BusinessPhoneNoOld], after performing your suggestion from the first post, but I very likely have a problem with the syntax in the query SQL's view (I feel like such an idiot even after taking an intro to SQL class).

What should the SQL view have? Should it have a "SELECT" somewhere?
Avatar of Varda

ASKER

To answer your question about the one question per year (which gave me a good laugh) - you could say that.

I'm not a professional database developer. I'm a project manager who has a passion for databases. I've developed database full-time for seven years, but because I work in an industry that does not recognize database developers and because it didn't get me any promotion I decided to step away from it and do what I was trained to do - construction management.

But I refuse to throw my hands up in the air and not use the correct tool for my management efforts.

So there you have it, my life's story.

Thank you for your help tonight, I hope I'll manage to make it work. But I have a sneaking suspicion that even changing the phone number format is not going to help the ultimate goal in resolving a problem with my database. I'll probably have to start a new thread with the actual problem.


Varda

In the same module, outside the function you have have:

Sub RunQury()

     CurrentDB.Execute "Update MyTable Set [PhoneNo]= fnPhoneNo([PhoneNoOld])"  

     ' Note: you need to change table and field names first.

End Sub

While the cursor is clicked betwwn Sub ... End Sub, hit F8 gaun and again (step through) and see how it works.

FYI: You could also have

CurrentDB.Execute "Update MyTable Set [PhoneNo]= fnPhoneNo([PhoneNoOld])"  

in on click event of any button on a form as well.


Mike
Avatar of Varda

ASKER

When I run the module I get an error that highlights the first line of the module [Function fnPhoneNo(strPhone As Stgring) As String] and says "Compile error: User Defined type not defined"

Function fnPhoneNo(strPhone As Stgring) As String
                         spelling error----^

try:

Function fnPhoneNo(strPhone As String) As String
Avatar of Varda

ASKER

When I tried the same code on a command button on a form I get run time error 3085 "Undefined function 'fnPhoneNo' in expression".  Seems to be related to the above error.
Avatar of Varda

ASKER

I corrected the typo but now get the second error I posted: 3085.

!

At the beginong of !\(999") "000\-0000;0;_

Causes the input mask to display from right to left, rather than from left to right, so we may need to change:

fnPhoneNo = Format(strPhone,"0000000000")

to

fnPhoneNo = strPhone

Looking more to figure out what the trailing portion means (;0;_).

Mike
Avatar of Varda

ASKER

Mike,

The input mask is the automatic one that Access provides when clicking on the three dots next to input task for any phone numbers. So I can't answer you on what the trailing portion means. :-(

Varda
Add end if for IF... Then(I guess I need to change my job as well)
Function fnPhoneNo(strPhone As String) As String

if len(strPhone)<>0 Then

strPhone = Replace(strPhone,"(","")
strPhone = Replace(strPhone,")","")
strPhone = Replace(strPhone,"-","")
strPhone = Replace(strPhone," ","")

end if

fnPhoneNo= strPhone

End Function
Avatar of Varda

ASKER

I made the change and added the end if but I'm still getting the runtime error 3085.

And please don't change your job!!! Well at least not until I get this straightened out anyway. :-)
step trough the code to see which line is causing the problem.
Also, because you may have some blank lines, change:

Function fnPhoneNo(strPhone As String) As String
 

to

Function fnPhoneNo(strPhone) As String

This way strPhone wouldn't cause proplem when there is a blank telephone number.

Mike
Avatar of Varda

ASKER

Mike,

I've changed the code (because I do have blank lines), but still getting an error (3085)

I've decided to give up on this question. Now my question is one of ettiquette? How do I award points if I decide to quit the question before it's resolved? Do I click on accept on the last response? In my own opinion you deserve all the points for your responsiveness and willingness to help me out, but what is the agreed convention on this website?

I've given up also because I found the larger source of my problem and it turns out not to be the phone number field but the email field. So I'm going to resolve this issue by deleting the email field. Who needs an email field anyway?!


Varda
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Varda

ASKER

Mike,

You're not getting off that easy. I'm about to post another question and it'll be the same amount of points and I really hope that you can answer the question, because THAT seems to be my bigger problem.

Varda
Avatar of Rey Obrero (Capricorn1)


Varda,

1. Open your table in design view,  place this

  (@@@) @@@-@@@@

  in the Format property of the field [BusinessPhoneNo].
2. create a new query,
   From the Show Table window, select the name of your table with the [BusinessPhoneNo] and [BusinessPhoneNoOld] fields

3.Then from Query Type drop down  click Update Query

4. place [BusinessPhoneNo] in the first column, like the one below ,

5.  Copy the whole thing in the Update To row

         Field:   BusinessPhoneNo
       Table :  YourTable
Update  To:   Replace(Replace(Replace(Replace([BusinessPhoneNoOld],"(",""),")",""),"-","")," ","")

6. Run the query by clicking on the Run button (the red exclamation point in the Tool bar)

7. finish