asked on

Update Query to format phone numbers in Access 2007

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

Rey Obrero (Capricorn1)

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

Rey Obrero (Capricorn1)

to remove any unpleasant characters, you can use replace()

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

will remove all the { - } from the phonenumber

kaufmed

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

netsmithcentral

ASKER

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

Rey Obrero (Capricorn1)

(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],")",") ")

netsmithcentral

ASKER

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.

Rey Obrero (Capricorn1)

is Phonenumber the name of the field? you have to use the name of the phone number field

netsmithcentral

ASKER

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?

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

netsmithcentral

ASKER

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.

netsmithcentral

ASKER

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