How to Use Advanced Filter in Access

Access’s most powerful type of filter is the Advanced Filter, which is used to sort multiple fields, apply complex filter criteria and expressions, as well as apply multiple AND/OR statements. Advanced Filters have many advantages. They have the ability to:

• Sort by multiple fields: You can sort records using several fields. For example, you could sort a table alphabetically by last name and then by first name.
• Use complex filter criteria and expressions: You can use advanced expressions and operators to search for data. For example, you could filter for dates that fall Between 1/1/95 And 12/31/99.
• Use multiple AND/OR statements: You can use more than one criterion to sift through records. For example, you could filter for employees who are from Washington AND who have been with the company for more than five years.

Apply an Advanced Filter

1. Open the table that contains the data you want to filter or sort and click the Advanced Filter Options button.
2. Select Advanced Filter/Sort.
   
3. Double-click each field you want to include from the field list.

   You can also drag fields from the field list onto the design grid to add fields or click the list arrow in the Field row of the design grid and select a field.

4. Click the Sort list arrow for the field and select a sort order (optional).
   
5. Enter any desired search criteria for the fields in the Criteria row.
6. Click the Apply Filter button.
   

The Advanced Filter is applied.

Common Criteria Operators
=	= "MN"	Finds records equal to MN.
< >	< > "MN"	Finds records not equal to MN.
<	<10	Finds records less than 10.
<=	<=10	Finds records less than or equal to 10.
>	>10	Finds records greater than 10.
>=	> = 10 AND < > 5	Finds records greater than or equal to 10 and not equal to 5.
BETWEEN	BETWEEN 1/1/17 AND 12/31/17	Finds records between 1/1/17 and 12/31/17.
LIKE	LIKE “S*”	Finds text beginning with the letter “S.” You can use LIKE with wildcards such as *.