Solved

Update Query to format phone numbers in Access 2007

Posted on 2009-04-10
11
2,149 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

776 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