Verifying phone numbers

[looloo]

Hi,

I have a form for entering records into a table, which includes a phone number field. I can't use an input mask because the data entry is done by copying and pasting from Word documents, websites, and the like. Therefore the data is entered in various formats, e.g. (xxx) xxx-xxxx, (xxx)xxx-xxxx, xxx-xxx-xxxx, and so on.

How can I eliminate all spaces and symbols, and store the fields as numerical data only, so that they can easily be compared and checked for duplicates?



I need the simplest solution possible because time is of the essence.

Thanks!

[RuralGuy]

The field should be a text (string) field and creating a procedure that would strip out all but the 0-9 values should be easy.

[looloo]

Okay, thanks.

I figured that was the direction I need to go, but I have never actually created procedures in Access, so I need some advice on how to do that. I'm using the 2010 version.

Thanks again.

[orange]

Here's a function and a test procedure that may be helpful.
I'm removing any ( ) or - and spaces. The resulting string must be all digits and 10 chars long. You can adjust as you wish. Good luck.
I'm using Acc2003 on XP

Public Function PNum(wrkValue As String) As String
Dim strValue As String
strValue = wrkValue
On Error GoTo PNum_Error

strValue = Replace(strValue, "(", "")
strValue = Replace(strValue, ")", "")
strValue = Replace(strValue, "-", "")
strValue = Replace(strValue, " ", "")
If (Len(strValue) = 10 And Not strValue Like "*[!0-9]*") Then
PNum = wrkValue & " resolves to valid phone " & strValue
Else
PNum = wrkValue & " Fail ***"
End If
On Error GoTo 0
Exit Function

PNum_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure PNum of Module AWF_Related"

End Function

test procedure

Sub testPNum()
Debug.Print PNum("(413)567-1271")
Debug.Print PNum("(713525-1471")
Debug.Print PNum("(713a425 1551")
Debug.Print PNum(" 813)425 1431")
Debug.Print PNum(" 813 6251781")
Debug.Print PNum(" 8 13 6251781")
Debug.Print PNum(" 8 1 3 6251x781")
End Sub

[looloo]

I'm not sure I even know where to enter the code... This is the most involved database I've created in Access. Most of my experience is in Oracle. If you could direct me on where to look that would also be a help

[RuralGuy]

Put this is a Standard module:

Public Function NumericString(strIn As String) As String
'-- Filter all but numeric characters from strIn
   Dim Marker As Long
   If Len(strIn & "") > 0 Then
      For Marker = 1 To Len(strIn)
         NumericString = NumericString & FilterIt(Mid(strIn, Marker, 1))
      Next Marker
   Else
      '-- Do not attempt any conversion
      MsgBox "Invalid entry for NumericString filter", vbExclamation + vbOKOnly
   End If
End Function
Public Function FilterIt(InChr As String) As String
'-- Strip all but numeric characters
   Select Case InChr
      Case "0" To "9"
         FilterIt = InChr     '-- Valid character
      Case Else
         FilterIt = ""        '-- Strip this character
   End Select
End Function

[looloo]

Okay, now I'm getting somewhere. I have entered the code in a module. Now where to I call the procedure from?

[RuralGuy]

You could put it in the BeforeUpdate event of the control:
Me.ControlName.Text = NumericString(Me.Controlname.Text)
...using YourControlName of course.
You will also want to put it in an update query of the existing table to clean up the current records.

[waqas]

hi

call it in command button click event like below

Private Sub Command3_Click()
Me.txtget.Value = NumericString(Me.txtph.Value)
End Sub
txtph where you past different ph number
second txtget is where you will get only digits

[looloo]

I'm making progress.

I ran the update query, and the phone number column is clean of symbols now! So the procedure works.

The problem now is with the Before Update event handler. It runs the procedure but then won't insert the new record. It gives me a runtime error 2115.

[RuralGuy]

Change the BeforeUpdate code to:
Me.ControlName = NumericString(Me.Controlname)
...which just removes the .Text from both entries. It will default to the .Value property instead.

[looloo]

I still get the same error, and now the text in the control does not change.

I love this forum... such fast responses!

[RuralGuy]

This is in the BeforeUpdate event right?

[RuralGuy]

Well darn. I guess you will need to move this:
Me.ControlName = NumericString(Me.Controlname)
...to the *AfterUpdate* event and see what you think. I tested it in ac2010 and it works.

[looloo]

YES! It works now. Thank you so MUCH!