Solved

Update Query to format phone numbers in Access 2007

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

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

8 Experts available now in Live!

Get 1:1 Help Now