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).
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).
- 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, "000000000 0")
' to store missing area codes as 000 543-2351
' how about the extensions?
End Function
Mike
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,
' to store missing area codes as 000 543-2351
' how about the extensions?
End Function
Mike
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
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---^
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?
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?
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?
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
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
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
spelling error----^
try:
Function fnPhoneNo(strPhone As String) As String
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.
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,"000000000 0")
to
fnPhoneNo = strPhone
Looking more to figure out what the trailing portion means (;0;_).
Mike
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,"000000000
to
fnPhoneNo = strPhone
Looking more to figure out what the trailing portion means (;0;_).
Mike
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
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
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
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. :-)
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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(Re
6. Run the query by clicking on the Run button (the red exclamation point in the Tool bar)
7. finish
- 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