May 03 2010

Microsoft Access Query Tip – Using Wildcards in Parameter Queries

Now for a second tip. This tip will be shorter, more advanced, and build on the first tip. Please read Intermediate Tip 1 first if you need a refresher course on Parameter queries.

One (misperceived) common notion about parameters is that you can’t use them with “wildcards.” A wildcard is a special character that you can use in a query to return specific rows without specifying an exact match in the criteria cell (WHERE CLAUSE) of the query. Wildcards are used with the “Like” operator in Microsoft Access to essentially replace characters (just like a “wildcard” in a deck of cards can represent any card). (For a complete explanation of wildcards look up “Like Operator” in Microsoft Access online help.) The most commonly used wildcard is the asterisk (*) which is usually just called the “star”. The star (*) “Matches any number of characters, and can be used anywhere in the character string.” MS Access Online Help. Or more simply, the star (*) wildcard “completes” a string. For example if you type a j* into the criteria cell of the [FirstName] field, and then move off of the cell Access will format the cell to read Like "j*". This would return all the rows where the FirstName field begins with the letter J. John, Jay, Jacob, Joseph. If you change the cell to read Like "jo*" the query would return John and Joseph but not Jay or Jacob. (Like "ja*" would return Jay and Jacob). Get the picture.

Now for our trick. The problem with the above examples is that the “j” (or “ja” or “jo”) is “hard-coded” into the query. Wouldn’t it be nice to be able to be able to parameterize that part of the selection criteria (as descibed in tip one). You can do this but there is a trick to doing it. I usually create this kind of query in steps. The end result is that you will have a query that uses both a prompt and wildcards. Here are the steps.

1) Create a normal query.

2) Click in the Criteria cell of the field that you want the promp with wildcard.

3) Type any string (a single letter will do) with an asterisk like this: J*.

4) Move off of the cell. Access will format the cell properly (for example – Like "J*").

5) Now select the string but not the star (in our example you would select just the letter J). Type to replace the selected string (in our example, J) with a parameter by typing a prompt and surrounding the prompt with square brackets (for example – type [Enter the first letter(s) of the first name]. The cell will now read:
Like "[Enter the first letter(s) of the first name]*"
THE QUERY WILL NOT WORK AT THIS POINT, THERE IS ONE LITTLE KNOWN LAST STEP.

6) KEY CONCEPT: In front of and behind the bracketed prompt place a “pipe”. The pipe is the “vertical bar” character that is above the backslash(\) on most keyboards. (I will explain the step more below.) Your criteria cell will now look like this:
Like "|[Enter the first letter(s) of the first name]|*"

Now run it and you should get the prompt but the star will also be appended to whatever you type. So if you type an “S” it will return rows with FirstName beginning with S. The reason the pipe characters are needed is because brackets are also used in a special way in conjunction with wildcards. The pipe characters essentially tell Access “Analyze me first” and forces it to prompt the user before evaluating the wildcard(s).