MS ACCESS FORM VALIDATION – PT4

In this video we will cover VBA validation functions like:

Validating Text Strings
Check the string for null values.
IsNull function

Check the length of a string.
Len Function

Check the presence of characters present in a string.
InStr function

Inpect a portion of a string.
Left, Right, Mid functions

Removing the leading or trailing spaces, or both, from a string of text.
Trim, LTrim, RTrim functions

Validating Numbers
IsNumeric function
IsDate function

Here is the video:

Private Sub btnNull_Click()
    'tests if the passed value is blank (nothing).
    If IsNull(Me.txtEmail) Then
        MsgBox "The value is blank."
    Else
        MsgBox "The value is not blank."
    End If
End Sub


Private Sub btnIsDate_Click()

    'tests if the passed value is understood as a date.
    If IsDate(Me.txtEmail) Then
        MsgBox "The value is a date."
    Else
        MsgBox "The value is not a date."
    End If
End Sub

Private Sub btnIsNumeric_Click()
    
    'test if the passed value is a number
    If IsNumeric(Me.txtEmail) Then
        MsgBox "The value is a number."
    Else
        MsgBox "The value is not a number."
    End If
    
End Sub

Private Sub btnLeft_Click()
    Dim strPart As String
    
    'gets the 3 characters from the beginning of the string
    strPart = Left(Me.txtEmail, 3)
    
    MsgBox "The left 3 characters are: " & strPart
End Sub

Private Sub btnMid_Click()
    Dim strPart As String
    
    'gets three characters from the text, starting at the fourth character
    strPart = Mid(Me.txtEmail, 4, 3)
    
    MsgBox "The Mid 3 characters from fourth character are: " & strPart
End Sub


Private Sub btnRight_Click()
    Dim strPart As String
    
    'gets the 4 characters from the end of the string
    strPart = Right(Me.txtEmail, 4)
    
    MsgBox "The Right 4 characters are: " & strPart
End Sub


Private Sub btnLen_Click()
    Dim intLen As Integer
    
    'tests the length of the string
    intLen = Len(Me.txtEmail)
    
    MsgBox "The length is: " & intLen & " characters"
    
End Sub

Private Sub btnTrim_Click()
    Dim strPart As String
    
    'removes unwanted spaces from the beginning and _
        end of a string of text
        
    strPart = Trim(Me.txtEmail)
    
    Me.txtEmail = strPart
    
    MsgBox "The text has been cleaned. "
    
End Sub


Private Sub btnInstr_Click()
    
    'check for a value in a string. Good for email validation
    If IsEmailAddress(Me.txtEmail) Then
        MsgBox "This is a valid email address"
    Else
        MsgBox "Please enter a valid email address"
    End If
End Sub

Function IsEmailAddress(email_address) As Boolean
    
    'is there the presence of an @ sign?
    If InStr(1, email_address, "@") Then
        'is there a ".com"?
        If InStr(1, email_address, ".com") Then
            IsEmailAddress = True
        Else
            IsEmailAddress = False
        End If
    Else
        IsEmailAddress = False
    End If
    
    
End Function

<< MS Access Validation – PT2 (explained) | How To Make A VBA Function>>



Leave a Reply