10 tips for using wildcard characters in Microsoft Access criteria expressions

This article is also available as a PDF download.

A wildcard is a special character that can represent one or
more characters in a text value. You can use wildcards to find many records
with similar, but not exactly the same, information. You can also use them to look
for a specific record when you can’t remember enough information to retrieve
just that one record.

The ability to find and retrieve data easily is great, but
use wildcards cautiously. If you use the wrong character, a query will almost
certainly return erroneous data. If you’re not lucky enough to catch the
mistake visually, the erroneous data could go undetected for a long time–long
enough to corrupt your data or even your entire application.

# 1: Match characters in a specific position

The most flexible wildcard character is the asterisk (*). It
matches any character or any block of characters in a specific position. For
instance, the following statement would return any entry that contains the
string access without regard to
letter case:

Like "*Access*"

So it would return the following entries: Microsoft Access, Access 97, and accessing.

Drop the first * character to match entries that begin with
the string access:

Like "Access*"

This statement would return Access 97 and accessing
but not Microsoft Access. Similarly,
the following statement would return Microsoft
Access but not Access 97 or accessing:

Like "*Access"

# 2: Spaces matter in a * match

When using the * character to match characters in a specific
position, pay close attention to space characters. If the search string
includes a space character between the literal characters and the * character,
Access will return only those entries that include a space character in that
position. For instance, the following statement matches Microsoft Access but not Access
97 or accessing:

Like "* Access"

You might not expect that search string to return Access 97, but the absence of the word accessing might be a surprise. Access won’t
return accessing because the entry
doesn’t have a space character before the access
block. Similarly, the following statement matches Access 97 but not Microsoft
Access or the single word entry, accessing:

Like "Access *"

# 3: Match just one character in a specific position

The question mark character (?) serves as a single-character
placeholder. You can combine this wildcard to specify a certain number of
characters. For instance, the following statement would match Smith and Smyth but not the word smooth
(without regard to letter case):

Like "Sm?th"

You can combine ? characters to cast a wider net or to focus
the search, depending on the circumstances. For example, the following criteria
would find Smith, smile, smite, and smirk:

Like "Smi??"

It would not find smirch
or smitten. That’s because each ?
character represents only one character. Although both smirch and smitten match
the literal characters in the expression (smi), both words have more than
two characters following those literal characters. To catch all entries that
begin with smi
without regard to the number of characters, use the * character (# 1).

# 4: Use ? to match symbol characters

Although the English language doesn’t use special
characters, like è and ü, it’s common to store values that contain such
characters. They pose a unique problem in a search, because you must use the
special character and not just the English counterpart. You might find it
easier to use the ? placeholder in these cases. For instance, if you want to
find the record for Comèrcio Mineiro,
you could take the time to insert the é character, but doing so requires extra
keystrokes. Instead, use the ? wildcard to represent the special character, as
follows:

Like "Com?rcio *"

This search expression might match a few other entries, but
in most cases, it won’t, because it’s such a narrow search. In this example,
you must also include the * or the literal string to match the desired entry
because it contains more than one word. If you omitted the *, Access would
match an entry that contained only the text Com?rcio.

# 5: Use [ ] to match literal wildcard characters

Sometimes, you’ll want to include a wildcard character as
part of a literal search string. For instance, you might use the * character to
denote a special note or comment in a memo or text field. In that case, you
must tell Access to evaluate the * character as a literal character rather than
a wildcard by enclosing the wildcard in brackets as follows

Like "[*]*"

If you use Like
“*”, Access will return every entry in the data source. (It isn’t
necessary to enclose the ! or ] wildcard characters in brackets.)

# 6: Match characters in a list

It’s possible to match more than one specific character at a
time using a list. To match any single character in a list, separate each item
in the list with a comma and enclose the list of characters in brackets. For
instance, the following search string will find any entry that begins with a or z:

Like "[a, z]*"

If the list is a set of consecutive values, use the hyphen
to separate the first and last characters in the sequence. The following
expression matches all entries that begin with the letters a, b, c, d,
or e:

Like "[a-e]*"

The bracket wildcards are flexible enough to handle multiple
lists as follows:

Like "[a-e, k, p-s]*"

The above expression matches all entries that begin with the
following letters: a, b, c,
d, e, k, p, q,
r, or s.

You can also use the bracket wildcards to denote characters
in the middle of a string. For instance, the following expression matches
entries that begin with the letter a
and have the letters b or f as the second character:

Like "a[b, f]*"

# 7: Exclude characters

Most of the wildcard characters match characters. The
exclamation point character (!) is different. Use ! to exclude matching
characters from a query’s results. For instance, the following expression would
return all entries that do not begin with the letter a:

Like "[!a]*"

Notice that the expression includes the bracket wildcards.
The ! character works with the brackets. In truth, you can exclude matches only
from a list. However, the list can contain just a single character. Expand the
list possibilities by applying the same techniques discussed in # 6.

# 8: Accommodate SQL conflicts

Access uses Jet SQL, a custom vendor-specific version of
SQL. Access Project files (.adp) use Transact SQL (T-SQL). SQL Server also uses
T-SQL. The two versions of SQL don’t support the same wildcard characters. That
means that an expression that works fine in an .mdb file won’t work in an .adp
file or in SQL Server. (If you should ever upgrade, wildcards could be a
problem.) The ActiveX Data Objects (ADO) and Data Access Objects (DAO)
libraries have a similar conflict. Table
A lists the most common Access wildcards and their ANSI-92 SQL counterparts
in a Project file and SQL Server.

Table A: ANSI-92 SQL wildcards

# 9: Accommodate both Jet and T-SQL

Working with both Jet SQL and T-SQL can be a problem.
Conditional compilation can help, at least where wildcards in code are
concerned. Use conditional compilation to switch between the two versions of
SQL, as needed. Enter the following code into the Declarations section of a
standard module:

'Conditional constants:


'Jet SQL = True


'T-SQL = False


#Const cSQL = True

'Declare and define wildcards for versions.


#If cSQL = True Then


Public Const cWildcardMultiple = "*"


Public Const cWildcardSingle = "?"


#ElseIfcSQL = False Then


Public Const cWildcardMultiple = "%"


Public Const cWildcardSingle = "_"


#End If

You can set the cSQL
flag manually, but you’ll probably want to automate the process if possible.
Unfortunately, this simple code catches only wildcards characters in code.

# 10: Delimit for both Jet and T-SQL

When searching for text, you must delimit the literal search
string. In Access, you can use the single (‘ ‘) or double quotation (”
“) marks to delimit literal characters. That can produce a conflict if
you’re also working with an Access .adp file or SQL Server tables because they
support only the single quotation mark as a text delimiter. For this reason, I
recommend that you use the single quotation mark delimiters in .mdb files, if
there’s any possibility that you might convert to an .adp file or upgrade to
SQL Server. It’s just one less modification you’ll need to make–finding and changing
all double marks to single quotation marks. (This tip is valid regardless of whether
you’re using wildcards.)

Susan Sales Harkins is an independent consultant and the
author of several articles and books on database technologies. Her most recent
book is Mastering Microsoft SQL Server
2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations
with Mike Gunderloy are Automating
Microsoft Access 2003 with VBA, Upgrader’s Guide to
Microsoft Office System 2003, ICDL
Exam Cram 2, and Absolute Beginner’s
Guide to Microsoft Access 2003, all by Que. Currently, Susan volunteers as
the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@setel.com.