Others

How to Create an Access Query to Find Records Older Than Eight Hours

Partner Editor Sep 25, 2026 870 views

Question details

The user needs to write a Microsoft Access query to retrieve database records whose timestamps indicate they are more than eight hours older than the current system time.

How to Create an Access Query to Find Records Older Than Eight Hours

Product: Microsoft Access
Device & OS: not provided
Scenario: Filtering database records based on a specific time elapsed since their creation or modification.
Observed behavior: The user requires the correct SQL date and time condition to subtract exactly eight hours from the current time.

Before you start

Verify the exact table name and the timestamp field name in your database, and ensure the target field is formatted with the Date/Time data type.

Solution 1Recommended

Use a SQL Date and Time Condition in Query Design

Applying a standard SQL query condition using the Now() function to filter out records based on a calculated time threshold.

Microsoft Access handles dates and times using specific formats. You can subtract a time duration directly from the Now() function by wrapping the specific time value in hash marks (#).

Open your Access database

Launch Microsoft Access and open the specific database containing the table you want to query.

Create a new query

Navigate to the 'Create' tab on the top Ribbon and click on 'Query Design'. Close the Show Table dialog to proceed to the SQL view.

Switch to SQL View

Right-click the new Query tab at the top of the workspace and select 'SQL View' from the drop-down menu.

Enter the SQL expression

Type the query: SELECT * FROM Contacts WHERE DateTimeStamp < Now()-#08:00:00#; (Replace 'Contacts' with your actual table name and 'DateTimeStamp' with your specific field name).

Run the query

Click the 'Run' button (the red exclamation mark icon) located in the Design tab to view the filtered records.

Use a SQL Date and Time Condition in Query Design

Syntax check: The expression Now()-#08:00:00# accurately calculates the date and time from exactly eight hours ago, ensuring your WHERE clause catches all records logged prior to that exact moment.

Free Microsoft Office alternative

Need a Powerful Yet Lightweight Office Suite for Data Analysis?

While Microsoft Access is a specialized relational database tool, WPS Spreadsheet offers excellent built-in tools for filtering, querying, and analyzing large datasets without the heavy database overhead. Enjoy a highly compatible and lightweight suite for all your document and data processing needs.

1. Download the installer: Visit the WPS Office website and download the free installation package for your operating system.
2. Install the software: Run the downloaded installer and follow the simple on-screen instructions to set up WPS Office.
3. Open WPS Spreadsheet: Launch the suite and open WPS Spreadsheet to start importing, filtering, and analyzing your data files seamlessly.

Completely free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel (.xlsx, .xls) filesAdvanced filtering and Pivot Table tools for rapid data sortingFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the DateDiff function instead to find records older than eight hours?

Yes, you can use the DateDiff function in your WHERE clause like this: DateDiff('h', [DateTimeStamp], Now()) > 8. However, subtracting the time literal #08:00:00# from Now() is often more straightforward and performs slightly faster.

How do I filter records older than a specific number of days instead of hours?

To filter records by days rather than hours, subtract an integer directly from the Date() or Now() function. For instance, to find records older than 5 days, use WHERE DateTimeStamp < Date() - 5.

Why is my query returning a 'Data type mismatch in criteria expression' error?

This error generally occurs if the field you are comparing (e.g., DateTimeStamp) is stored as 'Short Text' rather than a 'Date/Time' data type. You must open your table in Design View and change the field's data type to Date/Time.

Office Quick Solutions