Ms Access - Scheduling

[emmim44]

I like to see who is available on Sunday to take this shift that starts from 12 AM to 5:15 AM... The below query returns 0 even though there is a person who is available from Saturday 9:15 PM to Sunday 5:15 AM... How would I modify the query the pick that person ??
Any help appreciated..

xRouteStart = "12:00:00 AM" ,
yRouteEnd = "5:15:00 AM",
RunDays(i) = 'Sunday',
"And Driver_ID in (select ID from drivers where instr(Route_knowledge,'"&trim(RtID)&"')) " makes sure that person knows that shift.

Set Grab_AllSchedule = conn.Execute("SELECT * FROM schedule
WHERE " &_

 "(#"&FormatDateTime(xRouteStart)&"# between
sch_time_start and sch_time_end AND
#"&FormatDateTime(yRouteEnd)&"# between sch_time_start and
sch_time_end) " &_

 "And Driver_ID in (select ID from drivers where
instr(Route_knowledge,'"&trim(RtID)&"')) " &_

 "And sch_Day = '"&trim(RunDays(i))&"' " &_

 "And (Vacation <> 'Yes' or Vacation is null)" &_

 "And (sch_date between
#"&FormatDateTime(trim(x),2)&"# and
#"&FormatDateTime(trim(y),2)&"#) order by
tot_per_Day,Rand_ID,Driver_fn,Driver_ln asc")

[Dan_Bracuk]

Your code is difficult to read, but the logic that I would use is to get me all the people who are not scheduled for that time period. Somewhere along the way you will need a subquery. It will resemble one of these two examples.

select whatever
from whereever
where whatever
and somefield not in
(select thatfield
from etc)

select whatever
from whereever
where whatever
and not exixts
(select somefield
from etc)

[emmim44]

I solved the silly mistery... I have to construct a date to compare ... such as CDate(FormatDateTime("05/06/08 05:00:00 AM"))



Thank you Dan.