Running Regex searches in an Access database

If you need to run a query in an Access database using regular expression, Renaud Bompuis has posted a great solution here. In your Access database, press ALT + F11 to enter Visual Basic. Enter the below vba code in a new module. It will create a new function that you can reference in a query written in SQL.

For example, in this database, we have a table with numbers. We want to create a query which will find 7 digits numbers that begin with a '2'.

Go to Create . . . Query Design, and switch to SQL view. Structure a SQL search like this:

SELECT Field1

FROM Table1

WHERE RegexMatch(Field1, "2[0-9]{6}")

First, we SELECT the field we want in the results. Then we designate the table to be searched. The WHERE command lists the RegEx function, first referencing the field to be searched in parentheses.

Click Run on the toolbar, and your results will be displayed

As always, I have tested this script tonight in order to confirm that it works.

' ----------------------------------------------------------------------'

' Return True if the given string value matches the given Regex pattern '

' ----------------------------------------------------------------------'

Public Function RegexMatch(value As Variant, pattern As String) As Boolean

If IsNull(value) Then Exit Function

' Using a static, we avoid re-creating the same regex object for every call '

Static regex As Object

' Initialise the Regex object '

If regex Is Nothing Then

Set regex = CreateObject("vbscript.regexp")

With regex

.Global = True

.IgnoreCase = True

.MultiLine = True

End With

End If

' Update the regex pattern if it has changed since last time we were called '

If regex.pattern <> pattern Then regex.pattern = pattern

' Test the value against the pattern '

RegexMatch = regex.test(value)

End Function