Access date criteria fail most often because a field contains a time component, a literal date is interpreted in an unexpected format, or text is being compared with Date/Time data. Use typed parameters and a half-open range for predictable results.
Last updated: September 26, 2026.
PARAMETERS [pStart] DateTime, [pEnd] DateTime;
SELECT OrderID, OrderedAt, TotalAmount
FROM Orders
WHERE OrderedAt >= [pStart]
AND OrderedAt < DateAdd("d", 1, [pEnd])
ORDER BY OrderedAt;The start is inclusive and the next day is exclusive. That includes every time on the end date without relying on 23:59:59, which can miss higher-precision values.
Use literals only for fixed dates
Access encloses Date/Time literals in number signs, for example #2026-09-01#. The safest literal form in SQL view is an unambiguous year-month-day value. For user input, declare a DateTime parameter instead of concatenating a formatted string into SQL.
Microsoft’s date-criteria examples cover today, previous periods, ranges, and Null values. Remember that Date() returns today at midnight, while Now() includes the current time.
Do not wrap the indexed field unnecessarily
A criterion such as DateValue([OrderedAt]) = Date() is easy to read but applies a function to every stored value and can prevent efficient index use. Prefer OrderedAt >= Date() And OrderedAt < Date()+1. The same range pattern works for form controls in a date range query.
Troubleshoot empty or incorrect results
- Confirm the field data type is Date/Time rather than Short Text.
- Inspect whether values include times even when the form hides them.
- Declare parameters so Access does not infer them as text.
- Use Is Null separately when missing dates should be included.
If Access unexpectedly asks for a parameter, a misspelled field or form control may be the real cause. Follow the Enter Parameter Value troubleshooting checklist.
