Validation Check

Hi: Can any one please tell me how to i check the following for validation in VBA code. I mean are they valid or not
Phone Number
Social
Zip
Date

Thanks.
mustish1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
How do you define "Valid"?  This will check for blanks:

Private Sub Form_BeforeUpdate(cancel as integer)
     if nz(PhoneNumber,"")="" then
          msgbox "need phone"
          cancel = true
     end if
     if nz(Social,"")="" then
          msgbox "need social"
          cancel = true
     end if
'etc....
end sub
0
mbizupCommented:
add exit subs:

Private Sub Form_BeforeUpdate(cancel as integer)
     if nz(PhoneNumber,"")="" then
          msgbox "need phone"
          cancel = true
          exit sub
     end if
     if nz(Social,"")="" then
          msgbox "need social"
          cancel = true
          exit sub
     end if
'etc....

'** other before update code
end sub

   

0
rockiroadsCommented:
is this for US?

what format is the phone number in is it 999-9999-999 where 9 represents a numnber

For phone number see this  http://www.devx.com/vb2themax/Tip/19197

For Social Security Number, is it in the format of XXX-XX-XXXX ?
eg call this function, it returns true if in that format, false otherwise

Public Function IsValidSSN(ByVal sSSN As String) As Boolean

        IsValidSSN = (sSSN Like "###-##-####") Or _
            sSSN Like ("#########")

End Function


For date, just use   isDate(datevalue),  this returns true or false


For zip codes, is it just 5 numerics? u could use the like taken from the SSN example
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mustish1Author Commented:
Yes this is for US. I think the phone number must be >1 and for the social it must be 9 digits. Also zip will be 5 digits. How to i check the valid date. Also mbizup if you please tell me thru Not Null. I mean without nz

Thanks.
0
mbizupCommented:
Private Sub Form_BeforeUpdate(cancel as integer)
     if IsNull(PhoneNumber) then
          msgbox "need phone"
          cancel = true
          exit sub
     end if
     if IsNull(Social) then
          msgbox "need social"
          cancel = true
          exit sub
     end if
'etc....

'** other before update code
end sub

0
mustish1Author Commented:
>what format is the phone number in is it 999-9999-999 where 9 represents a numnber

999-999-9999

Thanks.
0
rockiroadsCommented:
Ok, did u get the check for social security number and date working okay?

Here is a function that I just knocked up that validates based on 999-9999-999

Public Function IsValidPhoneNumber(ByVal sPhone As String) As Boolean

    Dim sPhoneDetail() As String
   
    IsValidPhoneNumber = False
    If Len(sPhone) = 12 Then
        sPhoneDetail = Split(sPhone, "-")
            Debug.Print UBound(sPhoneDetail)
        If UBound(sPhoneDetail) = 2 Then
            If IsNumeric(sPhoneDetail(0)) And Len(sPhoneDetail(0)) = 3 And _
                IsNumeric(sPhoneDetail(1)) And Len(sPhoneDetail(1)) = 4 And _
                IsNumeric(sPhoneDetail(2)) And Len(sPhoneDetail(2)) = 3 Then
                IsValidPhoneNumber = True
            End If
        End If
    End If

End Function


Pass in your phone number and it returns true if in format of 999-9999-999, false otherwise

just zip code left right? what is a valid format of a zip code, is it just 5 digit check?

u could try this

if len(sZipCode)=5 and isnumber(sZipCode) = true then
    msgbox "valid zip code"
else
    msgbox "not valid zip code"
end if

0
rockiroadsCommented:
ok, just saw your last post

for dates, simply use  IsDate
0
mustish1Author Commented:
ok Thanks. I am testing the social and zip and the date. Only thing is that phone number is in that format "999-999-9999". But if some one send me numbers like that aaa9999765 or any number how to i check its validation. Should i use your function or the function which u send me as a link?
0
mustish1Author Commented:
Every thing is in USA
0
mustish1Author Commented:
I get an error:
ByRef argument type mismatch
on strNewPhone

        If IsValidPhoneField(Value:=mHomePhone, ReformattedPhone:=strNewPhone, IsRequired:=True, IsUSAPhone:=True) Then
            MsgBox "Valid mHomePhone-->" & strNewPhone
        End If


Function IsValidPhoneField(ByVal Caption As String, Value As Variant, _
    ReformattedPhone As String, Optional ByVal IsRequired As Boolean = True, _
    Optional ByVal IsUSAPhone As Boolean = True) As Boolean
    Dim intNbrDigits As Integer
    Dim strTemp As String
    Dim blnHasExtension As Boolean
    Dim strExtension As String
    Dim i As Integer

    On Error GoTo ErrorHandler
   
    IsValidPhoneField = True
    Value = Trim$(Value)

    If Value = "" Then
        If IsRequired Then
            IsValidPhoneField = False
        Else
            Exit Function
        End If
    End If

    If Len(Value) > 25 Then
        IsValidPhoneField = False
    End If

    If IsUSAPhone Then
        intNbrDigits = 0
        strTemp = ""
        blnHasExtension = False

        'Extract the extension if there is one.
        If Len(Value) <= 7 Then
            For i = 1 To Len(Value)
                If UCase$(Mid(Value, i, 1)) <> "X" Then
                    strTemp = Trim(strTemp) + Mid(Value, i, 1)
                End If
            Next
            If Len(strTemp) = 7 Then
                strTemp = ""
            Else
                blnHasExtension = True
                strExtension = Trim(strTemp)
                strTemp = ""
                Value = ""
            End If
        End If

        For i = Len(Value) To 1 Step -1
            If UCase$(Mid(Value, i, 2)) = " X" Then
                blnHasExtension = True
                strExtension = Mid(Value, i + 2, Len(Value) - (i + 1))
                Value = Mid(Value, 1, i - 1)
                Exit For
            End If
        Next

        'Change alphabetics to numbers ex. 1-94Perot
        For i = 1 To Len(Value)
            Select Case UCase$(Mid(Value, i, 1))
                Case "A" To "C"
                    strTemp = Trim(strTemp) + "2"
                Case "D" To "F"
                    strTemp = Trim(strTemp) + "3"
                Case "G" To "I"
                    strTemp = Trim(strTemp) + "4"
                Case "J" To "L"
                    strTemp = Trim(strTemp) + "5"
                Case "M" To "O"
                    strTemp = Trim(strTemp) + "6"
                Case "P" To "S"
                    strTemp = Trim(strTemp) + "7"
                Case "T" To "V"
                    strTemp = Trim(strTemp) + "8"
                Case "W" To "Y"
                    strTemp = Trim(strTemp) + "9"
                Case "0" To "9"
                    strTemp = Trim(strTemp) + Mid(Value, i, 1)
             End Select
        Next

        Value = strTemp

        intNbrDigits = Len(Value)

        ' Format 123-4567
        If intNbrDigits = 7 Then
            Value = Mid(strTemp, 1, 1) + Mid(strTemp, 2, 1) + Mid(strTemp, 3, _
                1) + "-" + Mid(strTemp, 4, 1) + Mid(strTemp, 5, _
                1) + Mid(strTemp, 6, 1) + Mid(strTemp, 7, 1)
        End If

        ' Format 1-234-567-8901
        If intNbrDigits = 11 Then
            Value = Mid(strTemp, 1, 1) + "-" + Mid(strTemp, 2, 1) + Mid(strTemp, _
                3, 1) + Mid(strTemp, 4, 1) + "-" + Mid(strTemp, 5, _
                1) + Mid(strTemp, 6, 1) + Mid(strTemp, 7, _
                1) + "-" + Mid(strTemp, 8, 1) + Mid(strTemp, 9, _
                1) + Mid(strTemp, 10, 1) + Mid(strTemp, 11, 1)
        End If

        ' Format 123-456-7890
        If intNbrDigits = 10 Then
            Value = Mid(strTemp, 1, 1) + Mid(strTemp, 2, 1) + Mid(strTemp, 3, _
                1) + "-" + Mid(strTemp, 4, 1) + Mid(strTemp, 5, _
                1) + Mid(strTemp, 6, 1) + "-" + Mid(strTemp, 7, _
                1) + Mid(strTemp, 8, 1) + Mid(strTemp, 9, 1) + Mid(strTemp, 10, _
                1)
        End If

        ' Append extension
        If blnHasExtension Then
            Value = Trim(Value) + " x" + Trim(strExtension)
        End If

        Value = Trim(Value)
        If intNbrDigits = 0 Or intNbrDigits = 7 Or intNbrDigits = 10 Or _
            intNbrDigits = 11 Then
        'do nothing
        Else
            IsValidPhoneField = False
        End If
    End If

    ReformattedPhone = Value
ExitMe:
    Exit Function
ErrorHandler:
    Err.Raise Err.Number, "IsValidPhoneField", Err.Description

End Function
0
mustish1Author Commented:
For SSN it goes to else condition. But it is 9 numbers and its valid
      mSSN1="123456789"
        If IsValidSSN(mSSN1) Then
            MsgBox "Valid"
        Else
            MsgBox "Invalid"
        End If

Public Function IsValidSSN(ByVal sSSN As String) As Boolean

        IsValidSSN = (sSSN Like "###-##-####") Or _
            sSSN Like ("#########")

End Function

Thanks.
0
rockiroadsCommented:
Hi
are you saying u can have alphabetical characters in your telephone number ?

aaa9999765



regarding ssn
what are valid characters for it?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rockiroadsCommented:
ok valid digits for SSN is 9 digits, and can be expressed as 999-99-9999

taking the sample code from phone number, how about this?


Public Function IsValidSSN(ByVal sSSN As String) As Boolean

    Dim sSSNDetail() As String
   
    IsValidSSN = False
   
    '9 digit entered, then its valid
    If Len(sSSN) = 9 And IsNumeric(sSSN) = True Then
        IsValidSSN = True
    ElseIf Len(sSSN) = 11 Then
        sSSNDetail = Split(sSSN, "-")
        If UBound(sSSNDetail) = 2 Then
            If IsNumeric(sSSNDetail(0)) And Len(sSSNDetail(0)) = 3 And _
                IsNumeric(sSSNDetail(1)) And Len(sSSNDetail(1)) = 2 And _
                IsNumeric(sSSNDetail(2)) And Len(sSSNDetail(2)) = 4 Then
                IsValidSSN = True
            End If
        End If
    End If

End Function



regarding telephone number, if u want to allow alphabetical characters then remove the isnumeric check

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.