Validation Rule: Limit entry to alphanumeric characters plus: './+-(space)'

Instead of a validation rule, I would instead add the UDF below to a regular VBA module, and then use the Before
Update event in your form to test.

It would go something like this:


If RegExpFind(Me!NameOfTextbox, "[^0-9A-Z\./\+ -]", 1, False) <> "" Then
    MsgBox "Invalid character in textbox", vbCritical, "No soup for you!"
    Cancel = True
    Me.NameOfTextbox.SetFocus
End If

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True) 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    Dim RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Create instance of RegExp object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
        
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
    
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
    
End Function

Select allOpen in new window

matthewspatrick ~

Thank you for your reply.  I actually have a rule I'm using in the BeforeUpdate event for the field's text box that works pretty well:

        'Does the VIN conform to code39?
        sPattern = "*[!0-9A-Z./+$% -]*"
        If (Me.VendorItemNumber) Like sPattern Then 'NOT WITHIN CODE39 SYMBOLOGY
                MsgBox "You have entered an INVALID CHARACTER in this field." & vbCrLf & "Vendor Item Numbers must conform to Code39 symbology.", vbCritical, "Invalid Character!"
                Cancel = True
                Exit Sub
        End If


I'm looking for a way to write a validation rule for the table field, in case someone attempts to defeat the form's test.

???

I've had the pleasure of meeting Allen, he is a giant in our field :)

I could not remember that ! was the "not" operator for Access's "light" version of regular expressions used
for the Like operator.  Oh well :)