Scheduling Question - Ms access

[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")

[OBP]

emmimm, I am not very good with SQL, any chance of posting a zipped database with some dummy data so that I can create the Query in design mode?
It might be a good idea to set your times etc to variables before putting them in to the SQL, so that the Formatting is done first.
I would also suggest that you have a look at this Thread
http://vbaexpress.com/forum/showthread.php?t=19418
where rangudu uses the Application.BuildCriteria to get the correct syntax for his SQL

[JimmyTheHand]

I would put the SQL statement into a String variable and print it out to debug window, just for to see what the actual SQL query is. E.g.
Dim mySQL As String
mySQL = "SELECT * FROM etc."
Debug.Print mySQL
Set Grab_AllSchedule = conn.Execute(mySQL) This way you can see how the SQL it is constructed, and whether it is really as you want it.

BTW, what are x and y in the last line of code?

Jimmy

[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 all.