Others

How to Query Date Values Stored with Time in Microsoft Access

Adam DavisAdam Davis Nov 3, 2026 869 views

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.

How to Query Date Values Stored with Time in Microsoft Access
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 you start

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.

Solution 1Recommended

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.

1
Open Query Design View

Right-click your query in the navigation pane and select 'Design View'.

2
Create a Calculated Column

In a blank column within the query grid, type an expression like `ExactDate: DateValue([YourDateField])` in the Field row.

3
Apply Date Criteria

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#`).

4
Run the Query

Click the 'Run' button (the red exclamation mark) in the Query Design ribbon to see the filtered results matching the exact date.

Use the DateValue Function to Isolate the Date
Pro Tip: Ensure that any user form inputs are properly converted to a valid date format before passing them into your query criteria to prevent type mismatch errors.
Free Microsoft Office alternative

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.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats for seamless data migration.Easily manipulate and query date and time values using familiar spreadsheet formulas like INT() and DATEVALUE().Lightweight architecture runs smoothly on Windows, Mac, and Linux without bogging down your system.Completely free to use with an intuitive, tabbed interface that requires zero learning curve.
microsoft office alternative - wps office

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.