About using wildcard characters

Microsoft Office Access 2003

Show All Show All

About using wildcard characters

You use wildcard characters as placeholders for other characters when you are specifying a value you want to find and you:

  • Know only part of the value.
  • Want to find values that start with a specific letter or match a certain pattern.

Wildcard characters are meant to be used with fields that have the Text data type. You can sometimes use them successfully with other data types, such as dates, if you don't use the Microsoft Windows Control Panel to change the regional settings for these data types.

If you are using Microsoft Jet database engine SQL in a Microsoft Access database, you can use the following wildcard characters in queries and expressions to find such things as field values, records, or file names. You can also use these characters in the Find and Replace dialog boxes in an Access database or a Microsoft Access project.

Character Description Example
* Matches any number of characters. It can be used as the first or last character in the character string. wh* finds 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, 123

To find such things as field values, records, or file names in a Microsoft Access project, or in a Microsoft Access database that uses Microsoft SQL Server-compatible syntax, use the following ANSI-92 wildcards.

Note  If you are using the Find and Replace dialog boxes in an Access project or in an Access database that uses Microsoft SQL Server-compatible syntax, you must use the Microsoft Jet SQL wildcards.

Character Description Example
% Matches any number of characters. It can be used as the first or last character in the character string. wh% finds 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

Notes

  • When using wildcard characters to search for other wildcard characters such as an asterisk (*), question mark (?), number sign (#), opening bracket ([), or hyphen (-), you must enclose the item you're searching for in brackets. If you're searching for an exclamation point (!) or a closing bracket (]), you don't need to enclose it in brackets.

    For example, to search for a question mark, type [?] in the Find dialog box. If you're searching for a hyphen and other characters simultaneously, place the hyphen before or after all the other characters inside the brackets. (However, if you have an exclamation point (!) after the opening bracket, place the hyphen after the exclamation point.)

  • You can't search for the opening and closing brackets ([ ]) together because Microsoft Access interprets this combination as a zero-length string. You must enclose the opening and closing brackets in brackets ([[ ]]).