Access Hacks

Hack #54. Use Regular Expressions in Access Queries

Sometimes wildcards aren't enough. With a little hacking, you can use regular expressions in your queries.

Although Access allows for some powerful string matching (see "Wildcard characters and the Like operator" in the Access Help system), sometimes you require an even more powerful solution. Microsoft added the ability to use regular expressions back in Version 5.0 of its Windows Scripting Engine, bringing it up to par with JavaScript. You can use this power inside an Access query as well.

Although the advanced details of regular expressions are beyond the scope of this hack, this example will get you started if you are new to the subject. If you need more information, I recommend the book Mastering Regular Expressions (O'Reilly).

In many cases it's possible to work around the lack of built-in regular expressions using Access's wildcard characters and multiple calls to different string functions, such as Left, Mid, Right, Len, and so on. However, once you see what you can do with a single custom function call, you can imagine the advanced possibilities and time savings.

Creating the Custom Function

The first thing we need to do is create a function that can be called from our Access query that ties into the Microsoft Scripting Runtime library.

Tip

This hack assumes the machine you are running has the latest version of Microsoft's Scripting Engine installed. If you are unsure, visit http://www.microsoft.com/scripting.

The following ...