validate phone number with "not like"???

  • Thread starter Thread starter tlyczko
  • Start date Start date
T

tlyczko

I have been reviewing the groups for ways to validate a phone number,
and I have found various techniques...

The one that seems to make the most sense is in the after update event:

If Me.Phone Not Like [###][-][####] then
msgbox 'wrong format'
end if

Where I am stuck is how to perform the test so the after update event
will kick out the right error correction.

I have found some keypress evaluation code but it seems too complicated
for this simple validation.

I have to allow the field to be null (no phone) or have the field be a
valid phone number...

Thank you,
:) Tom
 
Why not just use the Inut Mask Wizard for the text box where you take the
phone number is? It will require valid phone numbers.
 
Thank you, yes, I know about the InputMask wizard.

For various reasons, I must do the validation in CODE, in the
AfterUpdate Event.

The Input Mask doesn't do what I want for this purpose, validating the
phone number with CODE.

Thank you,
Tom
 
Aha,

the formatting of telephone numbers, well in England where I live we have
several different formats and they every 10 years or so because no one can
make their mind up on whets best.

but if you have a standard format e.g never changes then trapping things on
the lost focus event works in most instances

e.g.

you want to make sure that you have a "-" in fourth place then
if Mid(Me.phone.VALUE, 4, 1) <> "-" then
msgbox "telephone number incorrect!"
me.phone.setfocus
end if

check the mid command as I cannot remember if it counts from 0 or 1

hope that answers your question.
 
Sorry it took a while to get back to you, but I actually had to do some work :)
Try This:

Function ValidatePhone(strPhoNo As String) As Boolean

If IsNull(strPhoNo) Then
ValidatePhone = True
Exit Function
End If
If Len(strPhoNo) <> 8 Then
ValidatePhone = False
Exit Function
End If
If Not IsNumeric(Left(strPhoNo, 3)) Then
ValidatePhone = False
Exit Function
End If
If Not IsNumeric(Right(strPhoNo, 4)) Then
ValidatePhone = False
Exit Function
End If
If Mid(strPhoNo, 4, 1) <> "-" Then
ValidatePhone = False
Exit Function
End If
ValidatePhone = True
End Function
 
Shouldn't you be validating in the BeforeUpdate event, so that bad data
doesn't get into your database?
 
Hello Doug, thank you for writing...

I know you're right about the BeforeUpdate event...I will put it in
there.

OnExit always seems more intuitive than BeforeUpdate, but I have to
keep remembering that BeforeUpdate happens before OnExit...

Thank you also to Klatuu for the code snippet.

Thank you,
Tom
 
Here is another suggestion.

I had simular problem. Input mask just made some users red-mad.

What I ended up doing is run code that strips all the non-numeric characters
and then build the phone format of your choice.

here is snippet of code I used in my Word form
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Phone_fix()
Dim extraStriped As String
Dim fixed_CPhone As String
Dim myLen As Integer
Dim a As Integer
Dim tmpCHR As String
Dim OriginalPhone As String
a = 0
extraStriped = ""
OriginalPhone = ActiveDocument.FormFields("Phone_Num").Result
myLen = Len(OriginalPhone)
For a = 1 To myLen
tmpCHR = Mid(OriginalPhone, a, 1)
If IsNumeric(tmpCHR) Then extraStriped = extraStriped & tmpCHR
Next a

myLen = Len(extraStriped)
Select Case myLen
Case 10
fixed_CPhone = "(" & Left(extraStriped, 3) & ")" & " " & _
Strings.Mid(extraStriped, 4, 3) & " " & Right(extraStriped, 4)
Case 7
fixed_CPhone = "(___) " & Left(extraStriped, 3) & " " &
Right(extraStriped, 4)
Case Is > 10
fixed_CPhone = OriginalPhone
Case Else
fixed_CPhone = OriginalPhone
End Select
ActiveDocument.FormFields("Phone_Num").Result = fixed_CPhone
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I hope this helps

AvP
 
Back
Top