Hi,
Assuming [StartTime] and [EndTime] are date/time fields (i.e. not text fields), given your data, the following query should return [IDS] 3,4,5,6,7:-
PARAMETERS [Enter start time eg 14:00] DateTime, [Enter end time eg 22:00] DateTime;
SELECT ID, StartTime, EndTime
FROM tblData
WHERE IIf([StartTime]>[EndTime],1,[EndTime])>[Enter start time eg 14:00] AND [StartTime]<[Enter end time eg 22:00];
In these kinds of issues, the basic criteria to use is:-
[EndTime] > [RangeStart] AND [StartTime] < [RangeEnd]
The IIF in the criteria takes care of EndTimes that crossed midnight.
The criteria can also return these kinds of records not included in your data:-
StartTime / EndTime
13:00 / 23:00
21:00 / 6:00
If [RangeEnd] may also cross midnight, then you will have to use VBA to test for these "cross-midnight" issues and make necessary adjustments to [EndTime] and [RangeEnd] if needed before you can use the > and < operators. In this scenario, the adjustments are more involved than just changing [EndTime] to 1 as in my query above.
.