how to check availability

[pam2004]

Hi I am creating a system similar to a car hire system. I need to creat
a query to compare two dates RequiredDate and ReturnDate using te
dattdiff function to enable the query to display all cars/ that are no
already on hire for that period. Please help

 

[Allen Browne]

The cars booked for any part of the period between StartDate and EndDate
are found with:
[RequiredDate] <= [LastDate] And [ReturnDate] >= [StartDate]

You want the other cars:

SELECT * FROM tblCar WHERE NOT EXISTS
(SELECT BookingID FROM tblBooking
WHERE ([RequiredDate] <= [LastDate])
AND ([ReturnDate] >= [StartDate])
AND (tblBooking.CarID = tblCar.CarID) );