How to Query Date Values Stored with Time in Microsoft Access
Question details
The user needs to accurately query date fields that contain both date and time values in Microsoft Access without queries failing due to hidden time fractions.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Running an equality criteria query on a short-date display field where the underlying database storage includes a time value.
- Observed behavior
- Direct equality criteria fail to return records because the decimal portion (time) of the stored number prevents an exact match with standard date inputs.
Before modifying your queries, understand that Microsoft Access stores dates as double-precision numbers where the integer is the date and the decimal is the time. Changing the field's display format to 'Short Date' does not remove the underlying time data.
Use the DateValue Function to Isolate the Date
The DateValue function extracts only the date portion of a date/time field, allowing for accurate equality comparisons.
This approach is highly effective because it ignores the decimal time value and forces Microsoft Access to compare only the specific calendar date. It works regardless of how the date is formatted for display.
Right-click your query in the navigation pane and select 'Design View'.
In a blank column within the query grid, type an expression like `ExactDate: DateValue([YourDateField])` in the Field row.
In the Criteria row under this newly created column, enter the date you want to query, surrounded by hash symbols (e.g., `#12/01/2023#`).
Click the 'Run' button (the red exclamation mark) in the Query Design ribbon to see the filtered results matching the exact date.

Extract the Integer Portion with the Int() Function
Since Access stores dates as numbers, you can use the Int() function to strip away the decimal time value.
Manage and Analyze Your Data Easily with WPS Office
While Microsoft Access is a specialized database tool, many users find that managing and analyzing large datasets is much easier in a powerful spreadsheet application. WPS Office provides a lightweight, free alternative to Microsoft Office, featuring intuitive date-time functions for all your data analysis needs without the strict querying hurdles of Access.

Frequently Asked Questions
Why does my Microsoft Access query return no results when matching a specific date?
If your date field contains time values, an exact match query on just the date will fail. MS Access looks for an exact match of both the date integer and the time decimal. Use the DateValue() or Int() functions to filter by date only.
Does changing the field format to 'Short Date' fix querying issues?
No. Changing the display format only changes how the data looks on your screen. It does not alter the underlying stored double-precision number, meaning the time value is still present and evaluated during queries.
How are dates and times stored behind the scenes in Access?
Access stores Date/Time data types as double-precision floating-point numbers. The whole number (integer portion) represents the calendar date, and the fractional portion (decimal) represents the time of day.




