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

Hello ~

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

That's:
0-9
A-Z
.
/
+
(space)
-

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

Many THanks, Jacob
LVL 2
Chi Is CurrentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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

Open in new window

0
Chi Is CurrentAuthor Commented:
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.

???

0
Chi Is CurrentAuthor Commented:
OK.

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


And a site that provided some excellent assistance:
http://allenbrowne.com/ValidationRule.html

Thank you for your ideas.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
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 :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.