Solved

Validation Check

Posted on 2007-04-06
14
271 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
  • 6
  • 5
  • 3
14 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 150 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 150 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 350 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
 

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 150 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 350 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 350 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 350 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now