I am working on a vehicle database. We track when a vehicle is
borrowed and when it is returned. We also track the number of
kilometres travelled on a particular trip. There are several tables:
tblLeaders - lists the people, tblVehicle, tblresults - where the info
is stored for trips, tblVehicleType- we charge different rates for
different types of vehicles.
We have 2 main forms frmSignout and frmReturn. My problem occurs when
I want to sign out the same vehicle more than once on the same date.
The example would be that John signs out the vehicle at 10 a.m. and
returns it at 11 a.m. Mary then wants to use the vehicle at 2:00
until 4:00 the same day. The vehicle doesn't show up in the available
vehicles list. I'm using a General Date field for all of my dates.
The query used is as follows:
SELECT V.*
FROM tblVehicle AS V
WHERE v.vehicleid NOT IN (
SELECT DISTINCT R.VEHICLEID
FROM tblResults R
WHERE R.DateOut BETWEEN dateout.value and date_anticip_return.value
OR R.Date_Anticip_Return BETWEEN dateout.value and
date_anticip_return.value
OR (R.Dateout <= dateout.value AND R.date_anticip_return >=
date_anticip_return.value)
or R.DateRtn = 1/1/2004
);