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

Renaming Tables and Queries in Access Can Crash Your Database

While Access gives you the option to rename tables, queries, and other objects when you right click on them, you should avoiding doing so. After an object is renamed, you may receive an error such as this one:

Part of the trouble stems from the fact that other objects in the database will have trouble finding renamed objects that they have been linked to. If you select the option to 'Track name AutoCorrect info', and 'Perform name AutoCorrect' under File . . . .Options . . . Current Database, Access can avoid errors by updating object names automatically. But you need to give Access time to create the maps. Clear these options, close the database, reopen it, and re-select the options in order to make sure the maps are up to date.

Note the AutoCorrect feature will not revise object name changes in VBA code or queries written in SQL.

Parsing Data in an Access Column Into Multiple New Columns

The Tip of the Night for last night, showed how to parse data in an Access column. The code I posted specifically acted to pull data to the left of a delimiter. Here's how to parse out the data on both sides of a delimiter into two new columns.

In an example like this one, we have city and state names separated with tilde delimiters.

In the SQL mode of a query design view enter this code:

SELECT Table2.Field1, Trim(querysplit([Field1],"~",0)) AS Field2, Trim(querysplit([Field1],"~",1)) AS Field3 FROM Table2; We modify the number in the Trim function by entering a number to indicate the sequence of the text after a delimiter that we want to pull (so, 'querysplit([Field1],"~",1) pulls the first string after the ~ delimiter.)

We combine the two Trim functions by simply putting a comma between them on the line of code beginning with SELECT.

Note that as explained in last night's tip we need to have the following VBA code inserted in a module in order for this query to work:

Function QuerySplit(FieldName As String, Delim As String, Position As Integer) QuerySplit = Split(FieldName, Delim)(Position) End Function

When this query is run we end up with this result with the city and state names parsed into separate columns.