18

Regular Expressions and

Microsoft Access

Microsoft Access has provided a popular database management system for small businesses and other users for over a decade.

A fundamental task when using Access is the retrieval of data that is of interest to the user. With small quantities of data, it may be acceptable to return all data and allow the user to scan the data by eye for records that are of interest. However, once the volume of data increases much beyond the trivial, it is more efficient and more reliable to rely on Access’s queries, which include several wildcards (regular expression–like metacharacters) to select the desired data for display. Access wildcards can assist in filtering data retrieved by a query and improve the value of Access in many routine data retrieval uses for business.

In this chapter, you will learn the following:

The metacharacters supported in Access

How to use those metacharacters in Access queries

How to create select queries and parameter queries to apply the Access wildcards

To use wildcards in Access, you will need to understand at least a little about each of these aspects. Therefore, the presentation of material in this chapter isn’t entirely linear.

The Interface to Metacharacters in Microsoft Access

A principal use of wildcards in Access is in queries to match desired patterns of text. Options for using wildcards include in hard-wired queries where a specific query needs to be run frequently and in parameter queries where the user can enter a parameter that is used in the wildcard search he or she runs.

Chapter 18

This chapter explores both approaches and uses them to show examples of the various metacharacters that can be used in Access.

The examples make use of an AuctionPurchases sample database where a book collector with very eclectic tastes records his purchases from a fictional online auction house called dBeach. Most of the attention in the worked examples will focus on the ItemTitle and ItemAuthor fields, which, respectively, contain information about the title and author(s) of the purchased books. The book titles and their author(s) are real. All other aspects of the database are fictional, including dates and prices.

The examples in this chapter have been tested on Microsoft Access 2003. The interface shown in the examples is also the Access 2003 interface. Other versions of Access will appear similar but may not be identical.

Creating a Hard-Wired Query

This example assumes that you want to find item authors whose name includes the character sequence Hill or hill. Because matching in Access is case insensitive, either pattern will do for attempted literal matching.

The instructions given next are for Access 2003. Earlier versions may vary slightly in the detail of the interface.

Try It Out

Creating a Hard-Wired Query

1.Open Access 2003, and choose the Open option from the File menu.

2.Navigate to C:\BRegExp\Ch18, select the AuctionPurchases.mdb database, and click the OK button. If you downloaded the sample database to another location, navigate to the relevant directory.

Figure 18-1 shows the appearance just before the OK button is clicked in Step 2.

Figure 18-1

414

Regular Expressions and Microsoft Access

3.The AuctionPurchases sample database will open. In the Database Object window (see Figure 18-2), click Queries in the left pane, and in the right pane, you will see options to create a query in Design View and to create a query using a wizard. For your purposes here, the Design View option is more useful.

Figure 18-2

4.Double-click the Create Query in Design View option.

5.The Design View, including the Show Table dialog box (shown in Figure 18-3), will open. Highlight the dBeachPurchases table, and click the Add button.

Figure 18-3

6.Click the Close button to hide the Show Table dialog box. The dBeachPurchases table should be displayed in the upper part of the design window shown in Figure 18-4. This allows you to add columns from the dBeachPurchases table in the lower part of the design window.

415

Chapter 18

Figure 18-4

7.Click in the leftmost cell in the Field row of the grid in the lower part of the design window. A drop-down list is displayed. Select ItemTitle from the options offered.

Figure 18-5 shows the appearance while selecting a column in Step 7.

Figure 18-5

8.In the next column, select the ItemAuthor column from the options offered. Figure 18-6 shows the appearance while selecting ItemAuthor.

Figure 18-6

416

Regular Expressions and Microsoft Access

9.Switch to SQL View, using the menu shown in Figure 18-7. The menu is above and to the left of the design window. If it’s not visible, you may need to display the Query Design toolbar. To do that, go to the View menu, select Toolbars, and ensure that the Query Design Toolbar option is checked.

Figure 18-7

As you can see in Figure 18-8, the SQL code for a simple SELECT statement has been created for you.

The two selections that you made in Steps 7 and 8 produced the following SQL code:

SELECT dBeachPurchases.ItemTitle, dBeachPurchases.ItemAuthor

FROM dBeachPurchases;

If you are proficient in SQL code, you can simply add a WHERE clause to display only the desired rows, as follows:

SELECT dBeachPurchases.ItemTitle, dBeachPurchases.ItemAuthor

FROM dBeachPurchases

WHERE dBeachPurchases.ItemTitle LIKE “*Hill*”;

Sometimes, Access will add extra quotes or parentheses if you write SQL code. Sometimes, they have no effect on what the query does. Occasionally, they appear to significantly change the results that are returned.

However, for the purposes of this example, you will add the regular expression filtering in the design window.

417

Chapter 18

Figure 18-8

10.Type the following in the Criteria row in the design window:

Like “*Hill*”

The * metacharacter matches zero or more characters. Therefore, the pattern *Hill* will match zero or more characters occurring before the character sequence Hill (any case combination, because the default matching is case insensitive) and any number of characters occurring after the character sequence Hill. Figure 18-9 shows the result.

11.Save the query. Name it Hill in ItemTitle.

12.Open the SQL View, and inspect the code that has been created by the Access designer:

SELECT dBeachPurchases.ItemTitle, dBeachPurchases.ItemAuthor

FROM dBeachPurchases

WHERE (((dBeachPurchases.ItemTitle) Like “*Hill*”));

Notice how Access adds parentheses, which, to my eye at least, seem unnecessary.

418