Software

10 tips for using wildcard characters in Microsoft Access criteria expressions

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:

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

Access (.mdb) and DAO

ADO, Access Project (.adp), and SQL Server

Explanation

*

%

Matches any character or multiple characters in its position.

?

_

Matches any single character in its position.

[list]

[list]

Matches any character in list.

[!list]

[^list]

Excludes any character in list.

#

N/A

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:

'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.

 

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
annasudhakar
annasudhakar

Hi evry one i was face one interview qustion that was 

how to find the postion of "sudhakar" in that name 

"u"  postion  using wild  cards

rgletters
rgletters

Match "123" or "456": 

like "*[1,4][2,5][3,6]*"

chuuter
chuuter

Nice article. I have a question about parameterized SQL statements. I have to implement a simple search on a title field in an ACCESS database. I want the person to be able to put in a word and find all items with that word in the title. what would the SQL statement look like? What would be used to represent the parameter? Is Is it SELECT Orders.OrderID FROM Orders WHERE OrderTitle LIKE "*?*" how would I represent a parameter of any string length (I will be asking them to put in a word). For an Access 2003 database? I am using ASP.NET and VB.NET

JodyGilbert
JodyGilbert

Do you frequently use wildcard characters to locate records in Microsoft Access? What other tips would you add to this list?

kevinowpb
kevinowpb

One question? How do I make a query criteria to find a cell in a table that DOESN?T have an ?@? symbol and/or a 'period' to confirm if its an email address? (ie one cell in a list of emails that is missing the @ symbol and .(period), for instance.. so I can remove invalid email addresses)? Thank you!

b.schilling
b.schilling

Nice work, Susan. Your cautions at the end about the differences between Jet and T-SQL are well taken. Anyone have any notion why Microsoft hasn't been more aggressive in promoting the switch to Projects and T-SQL since it makes scaling up to SQL Server so much easier?

Editor's Picks