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

Microsoft Access

Avatar of undefined
Last Comment
Rey Obrero (Capricorn1)

8/22/2022 - Mon
Mike Eghtebas

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
Mike Eghtebas

- 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
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mike Eghtebas

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---^
Mike Eghtebas

Do you ask about one question per year?
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Mike Eghtebas


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
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"

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mike Eghtebas

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

try:

Function fnPhoneNo(strPhone As String) As String
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.
Varda

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mike Eghtebas

!

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
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
Mike Eghtebas

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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. :-)
Mike Eghtebas

step trough the code to see which line is causing the problem.
Mike Eghtebas

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Mike Eghtebas

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
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






I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck