Others

How to Find Records That Are at Least 21 Days Old in Microsoft Access

Phi Hung Vo Oct 9, 2026 868 views

Question details

The user needs to filter and display Microsoft Access records that were created 21 days ago or earlier.

How to Find Access Records That Are at Least 21 Days Old

Product: Microsoft Access
Device & OS: not provided
Scenario: Filtering database records based on a dynamic date range to find historical entries like past orders.
Observed behavior: The user wants to construct a query criteria that retrieves all records aged 21 days or more, rather than fetching only the records that are exactly 21 days old.

Before you start

Ensure your Microsoft Access database is open and identify the exact field name (e.g., 'order_date') that stores the dates you want to evaluate.

Solution 1Recommended

Use the DateDiff Function with Greater Than or Equal To Criteria

Apply the DateDiff function in your query's SQL or Design View to calculate the age of the record and filter for 21 days or more.

Using the DateDiff function allows you to calculate the exact difference between the current system date and the date stored in your records.

By pairing it with the '>=' (greater than or equal to) operator, you ensure that older records are also included in the results, preventing the query from only matching the exact 21st day.

Open Query Design

Open your Microsoft Access database, navigate to the 'Create' tab, and click on 'Query Design'. Add the table that contains your records.

Switch to SQL View

Right-click the query tab and select 'SQL View' to manually enter your criteria.

Enter the DateDiff Criteria

In the SQL statement, add a WHERE clause using the DateDiff function. Type: WHERE DateDiff("d", order_date, Date()) >= 21 (ensure you replace 'order_date' with your actual date field name).

Run the Query

Click the 'Run' button (the red exclamation mark) in the Design tab to execute the query and view the filtered results.

Use the DateDiff Function with Greater Than or Equal To Criteria

Using a Totals Query: If you are utilizing a totals query, make sure to select 'Where' in the Total row for this specific DateDiff criterion.

Free Microsoft Office alternative

Looking for a Simpler Way to Manage Data?

While Microsoft Access is designed for complex relational databases, everyday data tracking, filtering, and date calculations can often be handled more easily in spreadsheets. WPS Office offers a free, lightweight, and highly compatible alternative for your daily document and data management needs.

Seamless compatibility with Microsoft Excel (.xlsx), Word, and PowerPoint formats.Intuitive spreadsheet functions to filter dates and calculate differences without writing SQL.Lightweight design that runs smoothly on Windows, Mac, and Linux systems.Completely free to download with a familiar user interface for quick migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my query only return records from exactly 21 days ago?

If your query only returns records from the exact 21st day, you likely used the equals sign (=) in your criteria (e.g., DateDiff("d", order_date, Date()) = 21). Change the equals sign to greater than or equal to (>=) to include older records.

Can I filter records without using the DateDiff function?

Yes, in the standard Access query design grid, you can often achieve the same result by simply typing <= Date() - 21 in the criteria row under your date field, which calculates all dates occurring 21 days before today or earlier.

How do I find records that are at least 1 month old instead of days?

To filter by months, change the first argument of the DateDiff function from "d" (days) to "m" (months). The criteria would look like WHERE DateDiff("m", order_date, Date()) >= 1.

Office Quick Solutions