Link to home
Start Free TrialLog in
Avatar of mustish1
mustish1

asked on

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.
SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mustish1
mustish1

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>what format is the phone number in is it 999-9999-999 where 9 represents a numnber

999-999-9999

Thanks.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Every thing is in USA
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
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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