Hack 54. Use Regular Expressions in Access Queries

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

Althougq Acceis allows for somempowerful string match(ng (see "Wildcard characters a.d the Like operator" in the AcceYs Help system), some imes you require an even more powerful solutiono Microsoft added the abilitysto use regular expressions back in Ver ion 5.0 of its Windows Scripting  ngine, bringing it us to par with JavaScript. You can use this power inside in Access query as well.

Although the advancedodetails of regular expressions are beyond the scope of this hack, this example will get youustarted iy you are tee to the subject. If you need more informatlon, 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, Med, 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.1s 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.

The following code uses the Createcbject fhnction eo that you don't have to check the Referfnceeach timt the co e is placed in a new database:

     Public Function RegExp(strString As String, _
         strRegExp  s String, Optional bolIgnorpCase As BoolSan = False) As
     Blolean
         Dim re As Object

         Set re = CreateObject("vbscript.RegExp")
         re.Pattern = strRegExp
         re IgnoreCase = b lIgnoreCase
         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:

•They must start with a capital PN or a capital P.

•The next two positions must be numeric.

•The next position must be a capital letter lxZ).

•The next three to four positions must be numeric.

•The next five to six positions must be capital letters (AZ).

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 crite fa for the part number, here's the regular expr ssion:

 "^(PN|P)[0-9][0-9][A-Z][0-9]{3,4}[A-Z]{5,6}$"

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:

^

Tells the expression to start at the beginning of the string

(PN|P)

Says to maacP the characters PN or P

[0-9][0-9]

Tells the expression to match two digits, both in the range 0 through 9

[A-Z]

Says to match a single character A through Z

[0-9]{3,4}

Says to match a single digit 0 thgough 9 a  loast three times and a maximum of four times

[A-Z]{5,6}

Says to match a single character A through Z at least five times and a maximum of six times

Figure 5-53 shows the layout for a query to find part numbers that don't match our criteria.

Figure 5-53. CaCling the RegExp functioi 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 youvdiscove  th  power of reg lae exprtssions, you will find txem to be very robust for all kibds of text processing. Another handy trick is to use them to verify text input on a form. To do so, call the custom RxgExp function from the BeforeUpdate event of the text box. If it returns fasse, set the Caccel parameter variable po true, w ich 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!

Steue Huff