Find a time between two other times

[dbgirod]

I have a table with from and thru in short time format( 07:00 am). I need to run a query that searches for people available between the from time and the thru time. Scenario: I query to find someone available between 07:30 am and 07:45 am for a patient using the parameters below, I come up with some returns, but not all those mds that are truly available during at least part of that time. For instance, Jan is available between 07:00 am and 07:40 am to take patients. But she is not in the query results. Each from time and thru time is a separate field in the table. I am using a form to enter the parameter data.

From Time:
>=[forms]![FRM AVAILABILITY DT]![FROM]

AND
Thru Time:
<=[forms]![FRM AVAILABILITY DT]![THRU]

Both being on the same line of criteria. And during one try on separate lines.

I have even tried:

>=[forms]![frm availability dt]![from] and <=[forms]![frm availability dt]![thru]

for the from criteria then the thru criteria then both.

Please help. I have tried this every imaginable way. Trying to get this to work has been driving me insane for days.

 

[JJOHNS]

set up a query. Your SQL should look like this:

SELECT Yourtable.FROM, Yourtable.THRU, Yourtable.OTHERSTUFF
FROM Yourtble
WHERE Yourtable.FROM >= [FORMS].[TESTFORM].[FROM] AND Yourtable.THRU <= [FORMS].[TESTFORM].[THRU];


The form TESTFORM would be a form the user could use to enter the times he is looking for.