Find Records Near Specific Times in a Microsoft Access Query
Question details
Find records near 6:00 AM and 6:00 PM on a selected date when timestamps do not match those times exactly.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Query date-time records taken every few minutes and use the results for a daily report.
- Observed behavior
- The user wants the first record at or after each target time, or records within a defined tolerance.
Identify the table and date-time field that contain the readings. The examples use a table named Readings and a date-time field named ReadingTime; replace those names with your own.
Find the first record at or after each target time
Use minimum timestamps to find the first available readings at or after 6:00 AM and 6:00 PM on the selected date.
This query searches only through the end of the selected day. It finds records at or after each target, not the closest records before or after it.
In Access, select Create > Query Design, close the table-selection dialog if it appears, and switch to View > SQL View.
Replace the SQL with the following, then replace Readings and ReadingTime with your table and field names: PARAMETERS [Enter Date] DateTime; SELECT * FROM [Readings] WHERE [ReadingTime] = (SELECT Min([ReadingTime]) FROM [Readings] WHERE [ReadingTime] >= DateValue([Enter Date]) + TimeSerial(6,0,0) AND [ReadingTime] < DateValue([Enter Date]) + 1) OR [ReadingTime] = (SELECT Min([ReadingTime]) FROM [Readings] WHERE [ReadingTime] >= DateValue([Enter Date]) + TimeSerial(18,0,0) AND [ReadingTime] < DateValue([Enter Date]) + 1);
Select Run and enter the date at the prompt. Check that the returned timestamps are the first available values at or after the two target times.

Filter records within a time window from a form
Use a form control and a time interval when any record within a tolerance, such as five minutes, is acceptable.
Review Exported Access Readings in WPS Office
WPS Office is a free, lightweight alternative for working with exported spreadsheet data. Its familiar interface and Microsoft Office format support make it easy to move files between applications. WPS does not run or repair Microsoft Access queries.

Frequently Asked Questions
Does the minimum-timestamp query find readings before 6:00 AM?
No. The >= condition starts the search at 6:00 AM, so earlier readings are excluded. Use a different query if you need the closest reading on either side of the target.
What happens if there is no reading after a target time?
The minimum-timestamp query returns no match for that target if there is no later reading before the selected day ends. The other target can still produce a result.
How do I change the five-minute tolerance?
In the ReadingTime criteria, change the 5 in DateAdd("n",5,Forms!myCriteriaForm!myStartDateControl) to the number of minutes you need.
Why does the form-based query return multiple records?
The criteria return every record in the time window, not just the first one. Use the minimum-timestamp query if you want the earliest available timestamp instead.




