Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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.
0
mustish1
Asked:
mustish1
  • 6
  • 5
  • 3
7 Solutions
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now