- Enter a value or expression, or pick a value from the list in other fields on the Look For tab.
- Delete some of the alternative criteria that records can meet by deleting one or more Or tabs.
- Select another value that you want the records to contain, and then click Filter By Selection
on the toolbar. Repeat this until you have just the records you want to see.
- Make sure you haven't selected values in more than one row at a time. Although you can do this, Microsoft Access interprets the filter criteria as follows: "exclude the first selected value OR the second selected value," instead of "exclude the first selected value AND the second selected value."
Instead, select the first value you want to exclude, right-click, and then select Filter Excluding Selection. Select the next value you want to exclude, and then repeat the previous step.
- Change the filter by pointing to Filter on the Records menu, and then clicking Advanced Filter/Sort. In the Criteria row of the design grid, change Or to And.
- Specify another value in the Filter For box, and then press ENTER. Repeat until you have the records you want.
- Add more fields to the design grid, and then specify criteria for them in the Criteria row.
- Delete one or more extra rows of criteria (the Or rows) and use expressions with And instead of Or.
The filter returns too few records.
- Add alternative criteria that records can meet on the Or tab (at the bottom of the window).
- If you have multiple criteria on the Look For or Or tab, delete the value or expression that's restricting the records you want to see.
- Remove the filter, select a value you're looking for, and then click Filter By Selection
on the toolbar. If necessary, repeat this until you have the records you want to see.
- If you're using Filter By Selection on a combo box field that gets its values from a value list, check if the bound column for the combo box is the first column displayed in the list. If it's not and you've selected as your criteria part of a value from the combo box's displayed column, Microsoft Access can't match the criteria to the data in the bound column. You need to either change the combo box so the bound column is the first column displayed in the list, or fill the combo box with values from a table or query.
- Remove the filter if it's currently applied, and then specify a different value or expression in the Filter For box.
- Switch to the Advanced Filter/Sort window to see if you specified more than one criteria that records must meet, or if you specified the wrong criteria.
- Delete criteria for one or more fields in the design grid.
- Add extra Or rows where you can specify alternative criteria.
- Use expressions with Or instead of And.
The filter returns the wrong records.
If the filter is returning the wrong records, make sure you're using the correct fields to specify the value you're searching for or to specify other criteria, and make sure you've entered the correct value or criteria. If you're filtering on fields from linked tables, the values you use in criteria for those fields are case-sensitive
- If you're using expressions in the Filter By Form window or Advanced Filter/Sort window, verify you're using the correct operators.
- If you're specifying multiple criteria in the Advanced Filter/Sort window, verify you've entered it in the correct rows and columns in the design grid.
The filter didn't sort the records in the correct order.
You may need to:
- Add or delete a sort order.
- Sort by a different field.
- Change the sort order from Ascending to Descending, or vice versa.
- In the Advanced Filter/Sort window, rearrange the columns in the design grid, and then specify a sort order on one or more fields. Microsoft Access sorts the records starting with the leftmost column in the grid.
Notes
- If you have numbers in fields with a Text data type, those numbers will not sort from lowest number to highest, or vice versa. Instead, they will sort as follows: 1, 11, 12, 2, 20, 3, and so on. You must pad the single-digit numbers with a leading zero for the strings to be sorted properly.
- If you have Null values in a field that you're sorting in ascending order, records with those values appear first in the sort order before any records with text or numeric values in the sorted field.
I can't use an existing query as a filter.
A query must meet certain requirements if you're going to use it as a filter. The query:
- Must be based on the same underlying table or query as the form or datasheet to which you're applying it.
- Can't include other tables or queries.
- Must be a select query (not a crosstab query or action query).
- Can't include fields that sum, count, or calculate another type of total for the values in a field.
Some of the fields in the Filter By Form window are unavailable.
You can't use the following types of fields in the Filter By Form window because they don't apply to filtering records:
- A calculated control in a form.
- A field created with an unbound control or unbound object frame.
I don't have lists of values to pick from on one or more of the fields in the Filter By Form window.
How many records Microsoft Access reads depends on whether or not the field is indexed. If the field is indexed, Microsoft Access reads only the unique values, not all the values in a field. If the field isn't indexed, Microsoft Access reads all the values in the field. If the number of records it reads is more than the maximum it's allowed to display, which is determined by the setting for Don't Display Lists Where More Than This Number Of Records Read, Microsoft Access doesn't display the values for that field on the list. Consequently, you need to change the setting for this option. Because Microsoft Access reads all values in nonindexed fields, not just unique values, set this option to a number greater than (or equal to) the number of records in the nonindexed field with the most values.
Note When Microsoft Access is able to display the list in a field, it only shows the unique values, even for a nonindexed field.
The filter behaved correctly. When you create a filter on a subform, it only applies to the subform. To filter the records on the main form, click the main form, and then create the filter.
The filter behaved correctly. When you create a filter on a subdatasheet, it only applies to the subdatasheet. To filter the records on the datasheet that displays the subdatasheet, click in that datasheet, and then create the filter.
I can't display my records again after using the Data Entry command.
- On the Records menu, click Remove Filter/Sort.
My wildcard characters aren't working as expected.
In a Microsoft Access database, the wildcard characters can be different depending on the current setting of the ANSI SQL query mode.
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 ([[ ]]).