Regular Expressions in Access VBA

To use regular expressions in Access, make sure you add Microsoft VBScript Regular Expressions as a reference in your application. The following example was created in Access 2010.

Regular expressions are always a bit mysterious to the uninitiated. It helps to have a concrete example to get started. Once you break down the meaning of each piece of the expression, the whole thing starts making sense.

In this example, I'm validating social security numbers (SSNs). I wanted a validator that would work with or without the dashes. Here's the pattern used in the validator function:

re.Pattern = "^\d{3}-?\d{2}-?\d{4}$"

The "^" indicates the start of the line or string. The "\d{3}" means the SSN has to start with 3 digits. The "-?" means the 3 digits can be followed by 0 or 1 occurrence of a dash. "\d{2}" means there must be 2 digits following the dash (if there is one). Then we check for 0 or 1 occurrence of a dash again ("-?") followed by 4 more digits ("\d{4}"). The "$" indicates the end of the line or string.

If you wanted to validate dash-free SSNs, you would write an expression like this: "^d{9}$". This expression simply checks for 9 consecutive digits.

For more information on how to make your own pattern-matching expressions, see the Wikipedia article on regular expressions.

Here's the complete validator function:

Public Function validateSSN(ssn As String) As Boolean

    Dim re As RegExp
    Dim matches As MatchCollection
    Set re = New RegExp
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "^\d{3}-?\d{2}-?\d{4}$"
    Set matches = re.Execute(ssn)
    If matches.Count <> 1 Then
        validateSSN = False
        Exit Function
    End If
    
    validateSSN = True
        
End Function