Hack 54. Use Regular Expressions in Access Queries
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.
5.16.1. 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.
Public Function RegExp(strString As String, _ strRegExp As String, Optional bolIgnoreCase As Boolean = False) As Boolean Dim re As Object Set re = CreateObject("vbscript.RegExp") re.Pattern = strRegExp re.IgnoreCase = bolIgnoreCase If re.Test(strString) Then RegExp = True Else RegExp = False End If End Function
The function has two required parameters: the string being matched against and the string that contains the regular expression. The third, optional parameter tells the function whether to match the regular expression while ignoring the case; the default won't ignore the case.
5.16.2. Creating an Example Query
As an example, let's look at verifying part numbers by finding those that don't match a given criterion. Many times, you might receive data from multiple people and platforms that needs to be cleaned before going into a master database. Let's say that part numbers for a factory have the following criteria:
Examples of part numbers that meet the criteria include PN12W123ABCDE and P12W123ABCDE. Examples that don't meet the criteria include PN12W13ABCDE (only two digits after the W) and 12W123ABCDE (doesn't start with PN or P).
Given the set of criteria for the part number, here's the regular expression:
As mentioned earlier, these regular expressions can become quite overwhelming until you get used to them. If you aren't familiar with them, I strongly recommend additional reading to learn the full power of these expressions. To better understand it, let's break down this expression:
Figure 5-53 shows the layout for a query to find part numbers that don't match our criteria.
Figure 5-53. Calling the RegExp function from a query
Running the query in Figure 5-53 returns the part numbers that do not match our given criteria so that you can review them before placing them into a master database. Although you can do this without tapping into the power of regular expressions, it requires a much more involved solution.
5.16.3. Hacking the Hack
As you discover the power of regular expressions, you will find them to be very robust for all kinds of text processing. Another handy trick is to use them to verify text input on a form. To do so, call the custom RegExp function from the BeforeUpdate event of the text box. If it returns false, set the Cancel parameter variable to true, which clears the input on the text box.
You can even add an advanced feature to your application, which allows the user to do searches based on her own regular expressions!