MS Access Functions

Create a Date Range Query in Microsoft Access

A date range query returns records whose Date/Time value falls inside a period you define. The basic Access criteria are simple, but fields that also contain a time require a carefully chosen upper boundary.

Last updated: August 28, 2026.

Filter between two fixed dates

Assume an Orders table contains an OrderDate field. This query returns orders from January 1 through January 31, 2026:

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= #1/1/2026#
  AND OrderDate < #2/1/2026#
ORDER BY OrderDate;

The exclusive upper boundary—earlier than February 1—is deliberate. It includes January 31 values such as 1/31/2026 4:30 PM. A criterion ending with <= #1/31/2026# includes midnight at the start of January 31 but can omit records later that day.

Use start and end date parameters

A parameter query can prompt the user for both dates. Declare the parameter types so Access treats the answers as Date/Time values rather than text:

PARAMETERS [Enter start date:] DateTime,
           [Enter end date:] DateTime;
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= [Enter start date:]
  AND OrderDate < DateAdd("d", 1, [Enter end date:])
ORDER BY OrderDate;

The DateAdd expression advances the supplied end date by one day and uses that value as an exclusive limit. This is a reliable pattern when OrderDate may include a time.

Filter the current month

Use DateSerial to calculate the first day of this month and the first day of the next month:

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= DateSerial(Year(Date()), Month(Date()), 1)
  AND OrderDate < DateSerial(Year(Date()), Month(Date()) + 1, 1)
ORDER BY OrderDate;

This form also handles December correctly because DateSerial normalizes month 13 to January of the following year.

Enter the criteria in Query Design

  1. Open the query in Design View.
  2. Add the Date/Time field to the grid.
  3. In its Criteria row, enter the lower and upper comparison.
  4. For parameters, choose Design → Parameters and declare each prompt as Date/Time.
  5. Run the query and test records at the beginning and end of the period.

Common mistakes

  • Using text fields for dates: store values in a Date/Time field whenever possible.
  • Forgetting the time: use an exclusive next-day boundary for an inclusive end date.
  • Leaving parameters untyped: explicit Date/Time parameters avoid ambiguous prompts and improve compatibility with crosstab queries.
  • Filtering with formatted text: compare the original Date/Time field so indexes remain useful.

For more criteria examples, see Microsoft’s date criteria reference.

admin

admin

Related Posts