The most powerful filter is the . The is so powerful that you can think of it as a baby query. In fact, the procedure for creating an Advanced Filter is virtually the same as it is for creating a simple query. The problem with Advanced Filters is that they can be difficult to set up the first few timesespecially if you're new to Access.
Advanced Filters have many advantages. They have the ability to:
This lesson explains how to get your own Advanced Filters up and running.
If it isn't already open, find and open the Employees table.
Here's how to create an Advanced Filter/Sort:
Select Records 
The Filter window appears, as shown in Figure 3-8. The Advanced Filter will probably contain criteria from a previous filter that will have to be removed.
Click the 
Clear Grid button on the toolbar to clear the grid of any preexisting criteria.
You're ready to create your Advanced Filter. Notice in Figure 3-8 that the window is split. The top half contains a box labeled Employees, which displays all the fields in the current table. The bottom half of the screen contains a design grid, which is where the filter information goes.
The first thing you need to do is select the fields that you want to use in your filter. You can add fields to the design grid in two ways:
Because the field list doesn't have a lot of room, you will usually need to use the field list's scroll bar to scroll up or down the list.
Double-click the LastName field in the field list.
Access adds the Last Name field to the design grid.
Double-click the FirstName, City, HireDate, and Region fields to add them to the design grid as well.
You can use any field on the design grid to sort or filter the table. To sort by a field, click the Sort row in the column that contains the field that you want to sort and select Ascending or Descending from the list.
Click the LastName column's Sort row and select Ascending from the drop-down list.
This will sort the table by the LastName field in Ascending order. You can also sort by more than one field. For example, you could sort by LastName and then by FirstName. When you use several fields to sort a table, Access performs the sort in the order the fields appear in the design grid.
Click the FirstName column's Sort row and select Ascending.
Next you need to specify the criteria for the Advanced Filter. You type the criteria in the design grid's Criteria row.
Click the City column's Criteria row and typeLondon.
If you specify more than one criterion on the same Criteria row, Access treats it as an AND criteria statement, meaning a record must match all the criteria in order to be displayed. For example, you could filter for employees who are from Washington AND who were hired after January 1, 1993.
Click the HireDate column's Criteria row and type>1/1/93.
This criteria will display only records whose HireDate is greater than, or after, 1/1/93. Because it's on the same Criteria row as the City field's "London" criteria, the filter will display only those records whose City field equals "London" and whose Hire Date is after 1/1/93.
If you specify filter criterion on the Or rows, Access treats it as an OR criteria statement, meaning a record has to match the criterion on one row or the other to be displayed. For example you could filter for employees from California OR Minnesota.
Click the Region column's Or row and typeWA.
Your completed design grid should look similar to the one shown in Figure 3-9. You're ready to try the Advanced Filter.
Click the Apply Filter button on the toolbar to apply the filter.
The Advanced Filter window closes, and Access applies the filter and displays the records that meet your criteria.
Click the Remove Filter button to remove the filter.
