Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2211
  • Last Modified:

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

0
netsmithcentral
Asked:
netsmithcentral
  • 5
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
to remove any unpleasant characters, you can use replace()

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

will remove all the { - } from the phonenumber
0
 
käµfm³d 👽Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
netsmithcentralAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
(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
 
netsmithcentralAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
is Phonenumber the name of the field? you have to use the name of the phone number field
0
 
netsmithcentralAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:

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
 
netsmithcentralAuthor Commented:
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
 
netsmithcentralAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now