How To Create A Query In Access With Criteria: Step By Step Filter Method

I still remember building my first Access database in that basement workshop back in ’98. The real power came when I learned to filter data with criteria.

Creating a query with specific conditions transforms raw data into actionable insights. You’ll select the fields you need, then add criteria to filter results—like finding all customers in Denver or orders over $100.

This method works whether you’re using the query design view or writing SQL directly. The interface guides you through each step, making it accessible even if you’re new to databases.

The best part? Once set up, you can save and reuse these queries anytime, making your workflow significantly more efficient.

What Youll Need

Instructions

Here’s the method I use to filter data precisely in Access without writing complex code.

Launch Microsoft Access and open the database containing your tables. In the left navigation pane, locate the Queries section. If you don’t see it, click the dropdown arrow at the top of the pane and ensure All Access Objects is selected.

Right-click in the empty space under Queries and select Query Design. This opens the Query Design view with the Show Table dialog. Here’s the thing—this is where most beginners get stuck, so don’t rush this step.

In the Show Table dialog, select the table or tables containing the data you want to query. Click Add for each, then close the dialog. Your selected tables appear as small windows in the upper pane of the Query Design view.

In the lower pane (the design grid), you’ll see columns for each field you add. Click the dropdown arrow in the first Field row and select the fields you want to include in your results. To add multiple fields, move to the next column and repeat. I always include the primary key field even if I don’t need it in results—it helps with troubleshooting later.

In the design grid, locate the Criteria row under each field. This is where you specify conditions. For example, to find all records where the City field equals “New York”, type = “New York” in the Criteria row for the City column.

For more complex criteria, use operators like >, <, or Between. To find dates in a specific range, use Between #1/1/2023# And #12/31/2023#. If you need multiple conditions for the same field, place them in the same Criteria row. For OR conditions, use the row below (the or row).

Click the Run button (looks like an exclamation mark) in the ribbon to execute the query. The results appear in Datasheet view. If you don’t see the expected records, switch back to Design view and double-check your criteria syntax—Access is particular about quotes, parentheses, and date formats.

To save your query, press Ctrl+S, give it a descriptive name like Customers_NY, and click OK. Saved queries appear in the Queries section of the navigation pane and can be reused or modified later. This is the moment that matters: always test with a small, known dataset first to verify your criteria work as intended.

Tips and Common Mistakes

Small details make the difference between a query that works and one that leaves you scratching your head.

Step 1 Navigation: If you don’t see the Queries section immediately in the navigation pane, don’t panic. Click the dropdown at the top and select All Access Objects—this reveals everything, including the often-hidden Queries option. I’ve watched many users get stuck here, thinking their Access installation was incomplete.

Field Selection Strategy: Always include your primary key field in Step 2, even if you don’t need it in results. When I started skipping this, I spent hours debugging queries that returned duplicate or incomplete records. It’s your safety net for troubleshooting.

Criteria Syntax: Access is fussy about quotes and date formats in Step 3. Text values need straight double quotes, dates require pound signs, and there should be no spaces around operators. A missing quote or extra space is the most common reason queries fail to run.

Testing Method: In Step 4, always test with a small, known dataset first. I learned this after creating a complex query that worked perfectly on my test database but returned no results on the live one—turns out I’d used a field name that didn’t exist in production.

Frequently Asked Questions

Creating a query in Access with criteria is a powerful way to filter and analyze your data efficiently. With practice, you’ll master this skill and unlock deeper insights from your databases. Next, try experimenting with different criteria combinations to refine your results even further! 🎯