Simple SQL Query Line Help 1

[aircomba]

I am trying to do a query to my database that has 3 conditions.

First condition is to get all records where scheduleDate = dateEntered.

Then second I want to check those records with 2 time values to see
if either of those times exists between a start time and an end time in
the previously retrieved records. I am not exactly sure how to do this.

My current SQL line (which doesnt run):

sqlLine = scheduleDate=#1/1/2004# AND startTime BETWEEN 8:00:00 AM AND 8:30:00 AM

Most likely because the BETWEEN condition cannot be included with other conditions?
Maybe just the syntax is wrong? The startTime and endTime are DATE/TIME fields in the
database, so maybe I am searching wrong?

I would like the above to return any records that have the chosen date and then
also if either time1 or time2 are BETWEEN startTime and endTime in the database.

Any help at all would be greatly appreciated. ^^

 

[PHV]

Something like this ?
SELECT * FROM yourTable
WHERE scheduleDate=#1/1/2004#
AND (#8:00:00 AM# BETWEEN startTime AND endTime
OR #8:30:00 AM# BETWEEN startTime AND endTime)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[aircomba]

Wow so very simple, All I needed was to put my BETWEEN statements in ( ) gah! ^^

Thank you so very much for your help.

 

[PHV]

Always use parenthesis when you mix AND & OR operators in conditional expressions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLSister]

PHV, I agree. Even if the order of operations is what you want without the (), it makes it so much easier for someone else to maintain because they are sure of exactly what you intended.

Questions about posting. See faq183-874