?
Solved

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

Posted on 2008-11-15
4
Medium Priority
?
2,202 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:Chi Is Current
  • 2
  • 2
4 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22969296
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 22969315
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
 
LVL 2

Accepted Solution

by:
Chi Is Current earned 0 total points
ID: 22969330
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
 
LVL 93

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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