MS Access Query Between Two Dates: Filter Records Like a Pro

I spent three hours debugging a date query in Access last week. Turns out the issue was a simple format mismatch between the input and the database field. Here’s the thing—date filtering in Access looks intimidating, but it’s actually straightforward once you know the syntax.

Between the query designer and raw SQL, you have two solid options. The designer works great for quick filters, while SQL gives you more control. I prefer SQL for date ranges because it’s foolproof and easy to reuse across different queries.

You’ll learn to filter records between any two dates with precision. No more guessing why your query returns the wrong results. The method works whether you’re using a form, a parameter query, or hard-coded dates.

Works in all modern Access versions. I’ve tested this on Access 2016 through 2021 with consistent results.

What Youll Need

Instructions

Here’s the procedure I follow to filter records by date ranges cleanly and reliably.

Launch Microsoft Access and open the database containing your target table. I always start by backing up the file first—better safe than sorry when working with queries. In the Navigation Pane, locate the table you want to query and right-click it.

Select Query Design from the context menu, then click Close in the Show Table dialog since you’ve already selected your source. This opens a blank query grid where you’ll build your criteria.

In the query grid, add the fields you want to display by double-clicking them from the table list. Include the date field you’ll use for filtering—this is critical. For example, if your date field is named OrderDate, add it to the grid.

In the Criteria row under your date field, enter Between [Start Date] And [End Date]. The square brackets prompt Access to display input dialogs when you run the query. This syntax is case-insensitive but must be exact.

Click Run (the red exclamation mark) or press F5 to execute the query. Access will display two dialog boxes prompting you to enter the Start Date and End Date. Here’s the thing—use the format your database expects, typically mm/dd/yyyy for U.S. systems.

Enter the dates and click OK for each prompt. The query results will show only records where the date field falls within your specified range. Verify the output by checking the first and last records in the results.

To narrow results further, add more fields to the grid and set criteria in their respective rows. For example, add a Status field with the criterion “Completed” to filter only completed orders within the date range.

If you’ll reuse this query, click Save and give it a descriptive name like OrdersBetweenDates. Next time, just open the saved query, and Access will prompt you for the dates again. Real talk: this saves a ton of time if you run the same filter often.

For static ranges, replace the bracketed prompts in the Criteria row with hardcoded dates like Between #1/1/2024# And #12/31/2024#. The hashtags are mandatory for date literals in Access.

Tips and Common Mistakes

Here’s what I’ve learned from building countless queries in Access over the years.

The Syntax Secret: In Step 2, that Between [Start Date] And [End Date] syntax must be exact—Access is picky about the capitalization and spacing. I’ve seen queries fail because someone used “between” in lowercase or added extra spaces. The square brackets are what trigger the input prompts.

Date Format Matters: Real talk: Step 3’s date format requirement isn’t just a suggestion. If your database uses mm/dd/yyyy (standard for U.S. systems), entering dd/mm/yyyy will either return no results or the wrong ones. Always double-check your system’s default format before running.

Save Time with Static Ranges: For queries you use often, Step 4’s static date method with hashtags is a game-changer. I create versions of my most-used queries with hardcoded ranges like Between #1/1/2024# And #12/31/2024# for annual reports. Just remember—those hashtags aren’t optional.

Backup Before You Begin: I learned this the hard way after losing hours of work: that backup in Step 1 isn’t just good practice—it’s your safety net. Access queries can sometimes lock up tables during complex operations, and you’ll be glad you have that backup when they do.

Frequently Asked Questions

Mastering MS Access queries between two dates opens up powerful data-filtering possibilities! 🎯 With the right criteria, you can efficiently extract records for specific time ranges. Now that you’ve got the basics, try experimenting with more complex date conditions or explore parameter queries to make your filters even more dynamic!