Hi, all, I have the rent car inf

[site]

Hi, all,

I have the rent car info. form, I want to get info. about: "if the customer order car is not available 10:00am today, but I have the car available 10:00am tomorrow" I want to show up this info. to customer. The following are my queries:

nonAvailability:

SELECT qrySCHEDULE.vehicleid
FROM qrySCHEDULE left join usage ON
qrySCHEDULE.RESERVATION_ID = usage.RESERVATION_ID
WHERE (([forms]![pickdates]![allstart] Between dt_out
And dt_in or [forms]![pickdates]![allend] Between
dt_out And dt_in
or (qrySCHEDULE.dt_out between
[forms]![pickdates]![allstart] and
[forms]![pickdates]![allend])
or (qrySCHEDULE.dt_in between
[forms]![pickdates]![allstart] and
[forms]![pickdates]![allend])) and usage.date_in is
null)
UNION (SELECT qrySCHEDULE.vehicleid
FROM qrySCHEDULE inner join usage ON
qrySCHEDULE.RESERVATION_ID = usage.RESERVATION_ID
WHERE
datediff(&quot;d&quot;,(qrySCHEDULE.RESERVE_DATE_IN),([forms]![pickdates]![start]))<5 and
usage.date_in is null);

Availability:

SELECT VEHICLES.VEHICLE_ID, VEHICLES.YEAR, VEHICLES.MAKE, VEHICLES.MODEL, VEHICLES.TYPE, VEHICLES.dept, VEHICLES.[VEHICLE#], VEHICLES.ALT_FUEL, VEHICLES.[ACTIVE?], VEHICLES.PLATE
FROM VEHICLES
WHERE (VEHICLES.VEHICLE_ID) Not In
(select vehicleid from Nonavailable)
AND ((VEHICLES.TYPE)=[forms]![PickDates]![picktype]) AND ((VEHICLES.DEPT)=[forms]![PickDates]![pickdept]) AND ((VEHICLES.[ACTIVE?])=Yes)
ORDER BY VEHICLES.dept, VEHICLES.ALT_FUEL;
*****************************************************

I modified above queries to get &quot;after 24 hours&quot; have the car available:
new_nonavailability:

SELECT qrySCHEDULE.vehicleid
FROM qrySCHEDULE left join usage ON
qrySCHEDULE.RESERVATION_ID = usage.RESERVATION_ID
WHERE (([forms]![pickdates]![allstart=1] Between dt_out
And dt_in or [forms]![pickdates]![allend] Between
dt_out And dt_in
or (qrySCHEDULE.dt_out between
[forms]![pickdates]![allstart + 1] and
[forms]![pickdates]![allend])
or (qrySCHEDULE.dt_in between
[forms]![pickdates]![allstart + 1] and
[forms]![pickdates]![allend])) and usage.date_in is
null)
UNION (SELECT qrySCHEDULE.vehicleid
FROM qrySCHEDULE inner join usage ON
qrySCHEDULE.RESERVATION_ID = usage.RESERVATION_ID
WHERE
datediff(&quot;d&quot;,(qrySCHEDULE.RESERVE_DATE_IN),([forms]![pickdates]![start]))<5 and
usage.date_in is null);

new_availability:
SELECT [VEHICLES].[VEHICLE_ID], [VEHICLES].[YEAR], [VEHICLES].[MAKE], [VEHICLES].[MODEL], [VEHICLES].[TYPE], [VEHICLES].[dept], [VEHICLES].[VEHICLE#], [VEHICLES].[ALT_FUEL], [VEHICLES].[ACTIVE?], [VEHICLES].[PLATE]
FROM VEHICLES
WHERE (VEHICLES.VEHICLE_ID) Not In (select vehicleid from new_noavailability) AND ((VEHICLES.TYPE)=[forms]![PickDates]![picktype]) AND ((VEHICLES.DEPT)=[forms]![PickDates]![pickdept]) AND ((VEHICLES.[ACTIVE?])=Yes)
ORDER BY [VEHICLES].[dept], [VEHICLES].[ALT_FUEL];


Any help?

Thank you very much.
Jing