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

Posted on 2008-11-15
Last Modified: 2013-11-27
Hello ~

I'm writing a validation rule to limit acceptable characters to ONLY:  Like "[0-9A-Z./+ -]"


Using: Like "[0-9A-Z./+ -]" for the rule invalidates everything.  How can I write the rule to allow only these characters?

Many THanks, Jacob
Question by:Chi Is Current
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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
    End If

    Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
        Optional MatchCase As Boolean = True) 
        ' For more info see: 
        ' 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
                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)
                RegExpFind = Answer
            ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
                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
            RegExpFind = ""
        End If
        ' Release object variables
        Set RegX = Nothing
        Set TheMatches = Nothing
    End Function

    Open in new window

    LVL 2

    Author Comment

    by:Chi Is Current
    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.


    LVL 2

    Accepted Solution


    The following rule works: Not Like "*[!0-9A-Z./+ -]*"

    And a site that provided some excellent assistance:

    Thank you for your ideas.
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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 :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    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…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now