Solved

Validation Check

Posted on 2007-04-06
14
275 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DCount Type Mismatch 2 23
Opening forms with an input box 5 23
MS Access from Delphi 31 36
2 IIF's in Access query 25 35
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

825 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