Wildcards come in handy when you need to locate records with similar data or you're trying to track down a specific record based on limited information. But if you don't use the right wildcard characters the right way, you could wind up with the wrong data--a potentially app-corrupting nightmare. These tips will help you wield wildcards more effectively.
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:
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:
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:
# 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):
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:
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
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:
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:
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
Access (.mdb) and DAO
ADO, Access Project (.adp), and SQL Server
Matches any character or multiple characters in its position.
Matches any single character in its position.
Matches any character in list.
Excludes any character in list.
Matches the numeric digits 0 through 9.
# 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:
'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 = "_"
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 firstname.lastname@example.org.