Others

How to Include the Entire Final Date in an Access Date Range Query

Elise Williams Oct 8, 2026 869 views

Question details

The user needs to include records from the complete final date in a Microsoft Access date range query, which are currently being excluded because the date field contains time values.

How to Include the Entire Final Date in an Access Date Range Query

Product: Microsoft Access
Device & OS: not provided
Scenario: Filtering database records using a specific date range in an Access query.
Observed behavior: Records occurring on the final date of the specified range are excluded from the query results if they contain time values greater than midnight.

Before you start

Identify the specific Date/Time field in your Access table that you want to filter and ensure you have your query open in Design View.

Solution 1Recommended

Use an Exclusive Upper Boundary and Declare Parameters

Modify the date range criteria to add one day to the end date using a 'less than' operator, and explicitly declare the parameter data types to ensure all time values on the final day are included.

When an Access date field includes time values, entering a standard 'Between...And' criteria assumes a time of 12:00:00 AM (midnight) for the final date. This causes any record on that final day with a time later than midnight to be excluded. By configuring the criteria to calculate less than the following day, you capture the entire 24-hour period of your target end date.

Open Query in Design View

Open your Microsoft Access database, right-click the target query in the Navigation Pane, and select Design View.

Enter the Adjusted Criteria

Locate the Date/Time field in the query design grid. In the Criteria row for that field, enter the following expression exactly: >=[Enter the First Date:] And <[Enter the Last Date:]+1

Open Query Parameters

To prevent Access from treating your prompt as a mathematical expression, navigate to the Query Setup group on the Ribbon and click the Parameters button.

Declare the Parameters

In the Query Parameters dialog box, type [Enter the First Date:] in the Parameter column and select Date/Time in the Data Type column. Do the same for [Enter the Last Date:] on the next row.

Save and Run

Click OK to close the Parameters dialog box, then click the Run button (red exclamation mark) on the Ribbon to test your query.

Use an Exclusive Upper Boundary and Declare Parameters

Parameter Declaration: Declaring the parameters explicitly ensures Access treats your input as a date rather than standard text, allowing the '+1' operation to correctly add one day without triggering an expression error.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Your Office Needs?

While WPS Office does not include a direct alternative to Microsoft Access for database management, it provides powerful, free alternatives for Word, Excel, and PowerPoint. If you regularly export Access database reports to spreadsheets or documents, WPS Office handles Microsoft formats seamlessly with a lightweight, user-friendly interface.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite.
2. Install the Software: Follow the on-screen instructions to install the lightweight suite on your device.
3. Open Exported Data: Export your Access query results to an Excel file and open it seamlessly in WPS Spreadsheet for advanced data analysis.

Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight installation that uses minimal system resources.Free to use with a familiar, user-friendly tabbed interface for seamless migration.Perfect for analyzing, formatting, and presenting data exported from database management systems.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the 'Between... And' operator exclude my last date in Access?

If your date field includes time values, Access automatically assumes the end date is exactly at 12:00:00 AM (midnight). Any record on that day with a timestamp later than midnight falls outside the range and is therefore excluded.

Can I use the DateValue function to fix date range query issues?

Yes, you can use the DateValue() function to strip the time portion from your field (e.g., DateValue([YourDateField]) Between [Start Date] And [End Date]). However, this can significantly slow down query performance on large tables because Access must calculate the function for every single row during the query execution.

Why do I get a 'Data type mismatch in criteria expression' error when adding +1?

This error occurs when you append mathematical operators to a parameter prompt without explicitly defining the parameter's data type. You must declare the parameter as 'Date/Time' in the Query Parameters dialog so Access knows how to perform date arithmetic.

Office Quick Solutions