?
Solved

Validation Check

Posted on 2007-04-06
14
Medium Priority
?
282 Views
Last Modified: 2008-03-04
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
Comment
Question by:mustish1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 600 total points
ID: 18864957
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 600 total points
ID: 18864969
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 1400 total points
ID: 18864983
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:mustish1
ID: 18865075
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 600 total points
ID: 18865083
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
 

Author Comment

by:mustish1
ID: 18865089
>what format is the phone number in is it 999-9999-999 where 9 represents a numnber

999-999-9999

Thanks.
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 1400 total points
ID: 18865146
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 1400 total points
ID: 18865151
ok, just saw your last post

for dates, simply use  IsDate
0
 

Author Comment

by:mustish1
ID: 18865178
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
 

Author Comment

by:mustish1
ID: 18865186
Every thing is in USA
0
 

Author Comment

by:mustish1
ID: 18865201
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
 

Author Comment

by:mustish1
ID: 18865280
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 1400 total points
ID: 18865461
Hi
are you saying u can have alphabetical characters in your telephone number ?

aaa9999765



regarding ssn
what are valid characters for it?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18865582
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question