BETWEEN Clause does not select date

[iono]

ok heres the query
SELECT * FROM reservations WHERE LocationID = 2 AND (#10/14/2001 09:00:00# BETWEEN BookingStart AND BookingEnd) ORDER BY BookingStart;

there are two items in the database

1
bookingstart = 14/10/2001 10:30:00
bookingend = 14/10/2001 11:00:00
2
bookingstart = 14/10/2001 12:30:00
bookingend = 15/10/2001 13:00:00

if the search date is 14/10/2001 09:00:00 then no items are returned !

if the search date is 15/10/2001 09:00:00 only the second item is returned !

what am i doing wrong here ?

 

[tlbroadbent]

Doesn't appear anything is wrong. 14/10/2001 09:00:00 doesn't fall between bookingstart and bookingend of either record. 15/10/2001 falls between bookingstart and bookingend of the 2nd record. Why do you think the result is incorrect? There is not datetime that will select both records! Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[iono]

thanks .... doh !

what i am trying to do is to select records between two dates where a timed event occurs at some point between 9:00 and 21:00 on the day - any ideas ?

 

[tlbroadbent]

This query should handle start and end times in the range of 9:00 to 21:00. It will also handle times outside the range or bookings that span days.

SELECT *
FROM Reservations
WHERE (((Hour([BookingStart])) Between 9 And 21))
OR (((Hour([BookingEnd])) Between 9 And 21))
OR (((Hour([BookingStart]))<9)
AND ((Hour([BookingEnd]))>21)
AND ((Int([BookingEnd]))>=Int([BookingStart]))); Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.