About using wildcard characters to search for partial or matching valuesThe two ANSI SQL query modes, ANSI-89 and ANSI-92, are not compatible and use different wildcard characters. For example, if you run:
SELECT * FROM Customers WHERE Country Like 'U*'It returns all customers from a country/region named "U*", not all countries/regions beginning with the letter "U", because the asterisk (*) is not a wildcard character in ANSI-92 SQL.
SELECT * FROM Customers WHERE Country Like 'U%'It returns all customers from a country/region named "U%", not all countries/regions beginning with the letter "U", because the percent sign (%) is not a wildcard character in ANSI-89 SQL.
When you create a Microsoft Access database, you need to decide which query mode you are going to use because if you create a later query under a different ANSI SQL query mode than the current mode of your Access database, your query could produce runtime errors or unexpected results.
About using wildcard characters to search for partial or matching values
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 ([[ ]]).