Solved

Update Query to format phone numbers in Access 2007

Posted on 2009-04-10
11
2,157 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
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

730 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