Problem: I have a user that wants to be able to select phone calls that were received during a certain time period (i.e. #10:00 PM# to #04:00 AM#). As you know, queries will not select the records between these times with a Between (StartTime) And (End Time) Statement, because it crosses midnight (#00:00 AM#).

Optimal Solution: I want the user to be able to put in a beginning time and ending time and have the query select all records between these times regardless if it crosses Midnight.

Any help would be greatly appreciated.

tjs206

There isn't a simple answer for this if the range can sometimes cross midnight and sometimes not. I would suggest a function. The function should accept three parameters, the time being compared, the start time, and the end time. The function would return true or false. It would look like the following in your query:

Where YourFunction(SomeTimeField, [Enter Start Time], [Enter End Time]) = -1;

The function needs to compare the start and end times and if the end time is less than the start time (meaning the range crosses midnight), the test needs to be broken into two parts. Test for between Start and midnight and then testfor between midnight and end.

Thanks for your help. I was trying to make a mountain out of a mole hill. Here is what I did in the criteria section of the query for the time field:

Between ([Forms]![frmSelections]![cmbStartTime]) And (#11:59:59 PM#) Or Between (#12:00:00 AM#) And ([Forms]![frmSelections]![cmbEndTime])

From there it was just defining which start and end times had midnight between the two ([Forms]![frmSelections]![cmbStartTime] > [Forms]![frmSelections]![cmbEndTime]). Then using if then statements to determine which query to utilize.

This works great. I thought it would be a programming nightmare, but it wasn't.

Thanks again for the suggestion.

tjs206

[This message has been edited by tjs206 (edited 11-07-2001).]