Solved

Update Query to format phone numbers in Access 2007

Posted on 2009-04-10
11
2,166 Views
Last Modified: 2012-05-06
I've setup an update query to format phone numbers. The phone numbers are entered from a web form and come in to the database in different ways so I wanted to run a query to format whatever data comes in to (999) 000-0000 format and have it populate into a new column in the same table. I also want to run a separate query to strip characters from the entered data so that all that remains is a 10-digit number in another separate column. I've had success with the query I created to take a 10-digit number; 9990000000 and format it to (999) 000-0000 but other variations aren't working.  For instance; 999-000-0000 ends up looking like (999) -00-0000  and 999-000000 when I run the query. Can't seen to get it to strip the "-". It keeps the mid dash instead of omitting it and keeping the mid 3 digits. I should also restrict how the data is entered into the web form to avoid this in the future but I don't know how to do that either.

I've attached 2 different pieces of code that I used to create Modules in Access 2007.
I think I need to modify the different Case values in the attached.
Function Convert10Digit(phone As String)
          Dim PhoneLen As Integer
 
          On Error GoTo Convert10Digit_err
 
          ' Determine if a valid number exists. If not, exit function.
 
          If Len(phone) = 7 Or Len(phone) = 10 Or Len(phone) = 11 Or Len(phone) = 12 Or Len(phone) = 13 Or Len(phone) = 14 Then
             PhoneLen = Len(phone)
          Else
             Exit Function
          End If
 
          ' Format number and return value to function name.
 
          Select Case PhoneLen
             Case 7
                Convert10Digit = "(   ) " & Left(phone, 3) & _
                   "-" & Right(phone, 4)
             Case 10
                Convert10Digit = Left(phone, 3) & _
                   Mid(phone, 4, 3) & _
                    Right(phone, 4)
             Case 11
                Convert10Digit = Left(phone, 3) & _
                   Mid(phone, 4, 3) & _
                    Right(phone, 4)
             Case 12
                Convert10Digit = Left(phone, 3) & _
                    Mid(phone, 4, 3) & _
                   Right(phone, 4)
             Case 13
                Convert10Digit = Left(phone, 3) & _
                    Mid(phone, 4, 3) & _
                   Right(phone, 4)
             Case 14
                Convert10Digit = Left(phone, 3) & _
                    Mid(phone, 4, 3) & _
                   Right(phone, 4)
          End Select
 
Convert10Digit_exit:
          Exit Function
 
Convert10Digit_err:
          ' Use the following line if you are
          ' running Microsoft Access 7.0 or 97.
          MsgBox CStr(Err) & " " & Err.Description
          ' Use the following line if you are
          ' running Microsoft Access 2.0.
          ' MsgBox CStr(Error) & " " & Error(Err)
          Resume Convert10Digit_exit
 
       End Function
 
--
 
Function ConvertPhone(phone As String)
          Dim PhoneLen As Integer
 
          On Error GoTo ConvertPhone_err
 
          ' Determine if a valid number exists. If not, exit function.
 
          If Len(phone) = 7 Or Len(phone) = 10 Or Len(phone) = 12 Then
             PhoneLen = Len(phone)
          Else
             Exit Function
          End If
 
          ' Format number and return value to function name.
 
          Select Case PhoneLen
             Case 7
                ConvertPhone = "(   ) " & Left(phone, 3) & _
                   "-" & Right(phone, 4)
             Case 10
                ConvertPhone = "(" & Left(phone, 3) & ") " _
                   & Mid(phone, 4, 3) & _
                   "-" & Right(phone, 4)
            Case 12
                ConvertPhone = "(" & Left(phone, 3) & ") " _
                   & Mid(phone, 4, 3) & _
                   "-" & Right(phone, 4)
          End Select
 
ConvertPhone_exit:
          Exit Function
 
ConvertPhone_err:
          ' Use the following line if you are
          ' running Microsoft Access 7.0 or 97.
          MsgBox CStr(Err) & " " & Err.Description
          ' Use the following line if you are
          ' running Microsoft Access 2.0.
          ' MsgBox CStr(Error) & " " & Error(Err)
          Resume ConvertPhone_exit
 
       End Function

Open in new window

0
Comment
Question by:netsmithcentral
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24120387
what are the formats that you received from the web page?

post them here.

how do you want to format the phone numbers? in plain english..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24120460
to remove any unpleasant characters, you can use replace()

replace([Phonenumber],"-","")  

will remove all the { - } from the phonenumber
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24120569
You could use a function like the following to just grab the digits, then send that result to your format function/code:
Public Function GetDigitsOnly(ByVal number As String) As String
    Dim i As Integer
    Dim str As String
    
    For i = 1 To Len(number)
        Dim char As String
        
        char = Mid(number, i, 1)
        
        If IsNumeric(char) Then
            str = str + char
        End If
    Next
    
    GetDigitsOnly = str
End Function

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Author Comment

by:netsmithcentral
ID: 24125949
In terms of the web form. I was getting all sorts of combinations:
(999)999-9999
(999) 999-9999 (this is what I need in order to get the current Access module to work)
999.999-9999
999+999+9999
999-999-9999
9999999999

Below is a javascript I found to force the form entry to:
(999)999-9999 but NOW I need a space between the ")9" so it forces it to:
(999) 999-9999

Anyone know how to correct that?

<script language='JavaScript' type='text/javascript'>
<!-- Begin
var n;
var p;
var p1;
function ValidatePhone(){
p=p1.value
if(p.length==3){
	//d10=p.indexOf('(')
	pp=p;
	d4=p.indexOf('(')
	d5=p.indexOf(')')
	if(d4==-1){
		pp="("+pp;
	}
	if(d5==-1){
		pp=pp+")";
	}
	//pp="("+pp+")";
	document.form1.Phone.value="";
	document.form1.Phone.value=pp;
}
if(p.length>3){
	d1=p.indexOf('(')
	d2=p.indexOf(')')
	if (d2==-1){
		l30=p.length;
		p30=p.substring(0,4);
		//alert(p30);
		p30=p30+")"
		p31=p.substring(4,l30);
		pp=p30+p31;
		//alert(p31);
		document.form1.Phone.value="";
		document.form1.Phone.value=pp;
	}
	}
if(p.length>5){
	p11=p.substring(d1+1,d2);
	if(p11.length>3){
	p12=p11;
	l12=p12.length;
	l15=p.length
	//l12=l12-3
	p13=p11.substring(0,3);
	p14=p11.substring(3,l12);
	p15=p.substring(d2+1,l15);
	document.form1.Phone.value="";
	pp="("+p13+")"+p14+p15;
	document.form1.Phone.value=pp;
	//obj1.value="";
	//obj1.value=pp;
	}
	l16=p.length;
	p16=p.substring(d2+1,l16);
	l17=p16.length;
	if(l17>3&&p16.indexOf('-')==-1){
		p17=p.substring(d2+1,d2+4);
		p18=p.substring(d2+4,l16);
		p19=p.substring(0,d2+1);
		//alert(p19);
	pp=p19+p17+"-"+p18;
	document.form1.Phone.value="";
	document.form1.Phone.value=pp;
	//obj1.value="";
	//obj1.value=pp;
	}
}
//}
setTimeout(ValidatePhone,100)
}
function getIt(m){
n=m.name;
//p1=document.forms[0].elements[n]
p1=m
ValidatePhone()
}
function testphone(obj1){
p=obj1.value
//alert(p)
p=p.replace("(","")
p=p.replace(")","")
p=p.replace("-","")
p=p.replace("-","")
//alert(isNaN(p))
if (isNaN(p)==true){
alert("Check phone");
return false;
}
}
//  End -->
</script>

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24126262
(999)999-9999 but NOW I need a space between the ")9" so it forces it to:
(999) 999-9999

this will do it, in Access

replace([PhoneNumber],")",") ")
0
 
LVL 12

Author Comment

by:netsmithcentral
ID: 24126411
I've corrected the Javascript to force form field entries to this format:
(805) 999-9999

but I still need to correct the ConvertPhone and Convert10 modules.

in Access
" replace([Phonenumber],"-","")  " - this wiped out all of the phone number not just the "-" in access!

I know I'm reading this as replace the dashes in phone number with nothing but the query returned the result without even the phone number.

" replace([PhoneNumber],")",") ") "  - can't seem to get this to work but may not need it now since I've fixed the javascript.

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24126422
is Phonenumber the name of the field? you have to use the name of the phone number field
0
 
LVL 12

Author Comment

by:netsmithcentral
ID: 24127031
I know to edit that to the name of the field but then I get a "Data type mismatch in expression" error. The field is a text field. If I edit the Replace([text],"-","") then I get an "Enter Parameter Value - Text" window. Same as if I put in the word "phone".

Again in my access db I have a column named, "number"
Data in this field consists of previously entered phone numbers in various formats so the idea here is to take all these crazy formats:

(999)999-1234
(999) 999-1234
999+999+1234
999.999.1234
999-999-1234
999 999 1234

and force them into this:
9999991234

any ideas?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24127216

place this function in a regular module

Function GetNumbers(strIn As String) As String
Dim j, numOnly
If strIn = "" Then Exit Function
For j = 1 To Len(strIn)
    If IsNumeric(mid(strIn, j, 1)) Then
        numOnly = numOnly & mid(strIn, j, 1)
    End If
Next
GetNumbers = numOnly
End Function


to use in a query
select [numbers], getNumbers([number])  from TableX

in an update query

update tablex
set [numbers]=getnumbers([numbers])





0
 
LVL 12

Author Closing Comment

by:netsmithcentral
ID: 31569111
This module did the trick in stripping unwanted characters from my phone numbers in the db. This is combination with the other 2 modules produced the desired results.
0
 
LVL 12

Author Comment

by:netsmithcentral
ID: 24131168
These are the original modules with their corrections:
Function ConvertPhone(phone As String)
          Dim PhoneLen As Integer
 
          On Error GoTo ConvertPhone_err
 
          ' Determine if a valid number exists. If not, exit function.
 
          If Len(phone) = 7 Or Len(phone) = 10 Then
             PhoneLen = Len(phone)
          Else
             Exit Function
          End If
 
          ' Format number and return value to function name.
 
          Select Case PhoneLen
             Case 7
                ConvertPhone = "(   ) " & Left(phone, 3) & _
                   "-" & Right(phone, 4)
             Case 10
                ConvertPhone = "(" & Left(phone, 3) & ") " _
                   & Mid(phone, 4, 3) & _
                   "-" & Right(phone, 4)
            
          End Select
 
ConvertPhone_exit:
          Exit Function
 
ConvertPhone_err:
          ' Use the following line if you are
          ' running Microsoft Access 7.0 or 97.
          MsgBox CStr(Err) & " " & Err.Description
          ' Use the following line if you are
          ' running Microsoft Access 2.0.
          ' MsgBox CStr(Error) & " " & Error(Err)
          Resume ConvertPhone_exit
 
       End Function
 
--
Function Convert10Digit(phone As String)
          Dim PhoneLen As Integer
 
          On Error GoTo Convert10Digit_err
 
          ' Determine if a valid number exists. If not, exit function.
 
          If Len(phone) = 7 Or Len(phone) = 10 Or Len(phone) Then
             PhoneLen = Len(phone)
          Else
             Exit Function
          End If
 
          ' Format number and return value to function name.
 
          Select Case PhoneLen
             Case 7
                Convert10Digit = "(   ) " & Left(phone, 3) & _
                   "-" & Right(phone, 4)
             Case 10
                Convert10Digit = Left(phone, 3) & _
                   Mid(phone, 4, 3) & _
                    Right(phone, 4)
             
          End Select
 
Convert10Digit_exit:
          Exit Function
 
Convert10Digit_err:
          ' Use the following line if you are
          ' running Microsoft Access 7.0 or 97.
          MsgBox CStr(Err) & " " & Err.Description
          ' Use the following line if you are
          ' running Microsoft Access 2.0.
          ' MsgBox CStr(Error) & " " & Error(Err)
          Resume Convert10Digit_exit
 
       End Function

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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