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
