Filter Records Using Multiple Criteria

When a table contains many rows, the fastest way to “understand the data” is to temporarily narrow what you see. Microsoft Access provides two core tools for that purpose:

1. Sorting (reorders rows), and
2. Filtering (hides rows that do not match criteria).

In this lesson, you will use Filter by Form to filter a single table using multiple criteria.

A filter shows only the records that match your criteria. Everything else is hidden (not deleted). Filters are helpful when you want to answer questions such as:

• Which customers are in certain states?
• Which customers match a customer type?
• Which records fall within a date range?

If you need to filter by only one field and one value, Filter by Selection is quick. When you need multiple fields or OR logic, use Filter by Form.

Filter by Form is designed for “criteria building” without writing SQL. It displays a blank form-like grid (based on your table fields) where you can choose criteria from drop-down lists, type criteria (such as ranges), and combine conditions using AND and OR.

Follow this workflow to filter one table using multiple criteria:

1. Open the table you want to filter in Datasheet View.
2. On the Home tab, in the Sort & Filter group, open Advanced, then select Filter by Form.
3. Access displays the Filter by Form grid, with one “criteria cell” per field. Choose criteria from a drop-down list when available (for example, State = TX).
4. To add criteria that must be true at the same time, enter them on the same tab. This creates AND logic.
5. To add an alternative set of criteria (either/or), switch to the Or tab at the bottom and enter the alternate criteria there.
6. Click Toggle Filter (the funnel icon) to apply the filter. Access shows only matching records and indicates that the table is filtered.
7. To return to the full table, use Remove Filter (or toggle the filter off).

The rule is simple:

• Same tab (Look for) = criteria are combined with AND (all must match).
• Different tabs (Look for vs Or) = tabs are combined with OR (either tab can match).

Example:

• Look for: State = TX AND CustomerType = Retail
• Or: State = CA AND CustomerType = Retail

Result: Retail customers in TX or CA.

In Filter by Form, you often do not need to type the logical operators because Access infers them from where you place criteria. Still, it is important to understand the intent:

1. AND: record must satisfy all criteria on the same tab
2. OR: record may satisfy criteria on either tab
3. NOT: excludes matches (for example, Not "CA")

You can also use common criteria patterns in fields:

• Ranges: Between 100 And 500
• Wildcards: Like "Aus*" (values starting with “Aus”)
• Null checks: Is Null or Is Not Null

Filter by Form is excellent for ad-hoc filtering, but it is not always ideal when users need to run the same filtering logic repeatedly. In that situation, a common Access pattern is:

1. Build a form that collects criteria (combo boxes, list boxes, text boxes).
2. Use that form to build a query (or apply a filter) and display results.

This approach keeps the user experience simple while still supporting complex combinations of optional criteria.

• Use Filter by Form when you need multiple criteria across fields.
• Criteria on the same tab are combined with AND.
• Criteria on the Or tab create OR logic across alternative criteria sets.
• For repeatable filtering, consider a selection form that applies a query or filter for the user.