How to Create an Access Query with Start and End Dates
Question details
The user wants to filter database records within a specific date range by prompting for a start and end date in Microsoft Access.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Retrieving specific data records based on user-defined timeframes instead of relying on wildcard text searches.
- Observed behavior
- The user needs to implement a reliable date-range filter for more accurate data retrieval.
Ensure your database table contains a field formatted specifically as a Date/Time data type. Text-based dates will not filter correctly using standard date operators.
Use Parameter Prompts in Query Criteria
The quickest way to filter by a date range is to use bracketed prompts directly in the query design view using the BETWEEN operator.
This method creates a parameter query. When you run the query, Access will automatically prompt you to enter the specific start and end dates before displaying the results.
Open your Access database, navigate to the Create tab on the ribbon, and select Query Design. Add the table containing your data.
Double-click the fields you want to display in your results, ensuring you include the specific Date/Time field you wish to filter.
In the 'Criteria' row directly under your date field, type the following expression: BETWEEN [Enter Start Date] AND [Enter End Date].
Click the Run button (the red exclamation mark) in the Design tab. A dialog box will appear asking for the Start Date, followed by another for the End Date.
Link Query Criteria to a Custom Form
For a more user-friendly interface, create a dedicated form where users can input dates using unbound text boxes before running the query.
Manage and Filter Your Data Easily with WPS Office
While WPS Office does not include a complex relational database tool like Access, WPS Spreadsheet is a powerful, free alternative to Microsoft Excel. It handles large datasets efficiently and lets you filter date ranges with just a few clicks—no complex query syntax required.
- 1. Open your dataset: Launch WPS Spreadsheet and open your document containing the data and date columns.
- 2. Enable AutoFilter: Highlight your header row, navigate to the Data tab, and click the AutoFilter button.
- 3. Apply a date range filter: Click the filter drop-down arrow on your date column, select 'Date Filters', and choose 'Between' to easily specify your start and end dates via a calendar prompt.
Frequently Asked Questions
Why does my date parameter query return zero records when I enter valid dates?
This usually happens if the field data type in your table is set to 'Short Text' instead of 'Date/Time'. Access cannot evaluate text strings properly using the BETWEEN operator. Additionally, verify that the date format you are typing into the prompt matches your system's regional date settings.
Does the BETWEEN operator include the start and end dates in the results?
Yes, the BETWEEN operator is fully inclusive in Microsoft Access. Any records that fall exactly on the start date or the end date will be included in your query results.
How do I filter a date field that also contains specific timestamps?
If your Date/Time field includes timestamps (e.g., 10/15/2023 2:30 PM), a standard BETWEEN query might miss records occurring later in the day on your end date. To fix this, change your criteria to use greater-than and less-than symbols: >=[Enter Start Date] AND <[Enter End Date] + 1.
