Solved

Update Query to format phone numbers in Access 2007

Posted on 2009-04-10
11
2,147 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 
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 119

Expert Comment

by:Rey Obrero
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now