Page 11 of 19PREVNEXT

Queries IV: Working with complex criteria

Symbol Description Example
* Matches zero or more characters. It can be used as the first or last character in the character string. wh* finds wh, what, white, and why
? Matches any single alphabetic character. b?ll finds ball, bell, and bill
[ ] Matches any single character within the brackets. b[ae]ll finds ball and bell but not bill
! Matches any character not in the brackets. b[!ae]ll finds bill and bull but not ball or bell
- Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). b[a-c]d finds bad, bbd, and bcd
# Matches any single numeric character. 1#3 finds 103, 113, and 123

Wildcards can be used in queries when you know only part of a value, want to search for a value based on some of the letters or numbers it contains, or want to retrieve data that matches a pattern.

Simple query criteria, such as typing "owner" in the ContactTitle field, will only find data that is an exact match. If someone is an "owner/operator", the record will not be retrieved. Wildcards can extend the power of your queries beyond simple criteria.

Wildcards are symbols and characters that represent the data you are looking for. For example, a number sign (#)wildcard is used to represent any single numeric character.

When you use wildcards, you also use the Like operator. For example, Like A* would find all customers in your database whose last name starts with "A".

Note Wildcard syntax must be compatible with the database engine that you use. The syntax in the table on the left works with default Access databases, which are based on the Microsoft Jet database engine and use ANSI-89 wildcards.

However, if your database uses syntax compatible with Microsoft SQL Serverâ„¢, or if you are working with an Access project (an Access file that connects to a SQL Server database), you should use ANSI-92 wildcards, which you can find in the Quick Reference Card at the end of this course.

Page 11 of 19PREVNEXT