Question Need help with some SQL code

[helpme5]

Hi guys,

I am using some SQL code in a in a vehicle booking database which shows the user in a seperate form which vehicles are available to be booked for a select date.

However,
I really need to make it so it does this by date AND time. Does anyone know how I could modify my code to enable this to be done?

Thanks very much.

Here is my current code below:

PARAMETERS [Please enter start date] DateTime, [Please enter end date] DateTime;
SELECT Booking.Booking_start_date, Booking.Booking_end_date, Booking.BUSID, Vehicle.Registration
FROM Vehicle INNER JOIN Booking ON (Vehicle.BUSID = Booking.BUSID.Value) AND (Vehicle.BUSID = Booking.BUSID.Value)
WHERE (((Booking.Booking_start_date) Between [Please enter start date] And [Please enter end date])) OR (((Booking.Booking_end_date) Between [Please enter start date] And [Please enter end date])) OR (((Booking.Booking_start_date)<[Please enter start date]) AND ((Booking.Booking_end_date)>[Please enter end date]));

 

[namliam]

It is already working on Date AND time, date/time is one field....

Please post code readable though...

PARAMETERS [Please enter start date] DateTime, [Please enter end date] DateTime;
SELECT Booking.Booking_start_date, Booking.Booking_end_date, Booking.BUSID, Vehicle.Registration
FROM Vehicle 
INNER JOIN Booking ON (Vehicle.BUSID = Booking.BUSID.Value) 
                  AND (Vehicle.BUSID = Booking.BUSID.Value)
WHERE (((Booking.Booking_start_date) Between [Please enter start date] And [Please enter end date])) 
   OR (((Booking.Booking_end_date)   Between [Please enter start date] And [Please enter end date])) 
   OR (     ((Booking.Booking_start_date)<[Please enter start date]) 
        AND ((Booking.Booking_end_date)  >[Please enter end date])
      );

The .Value in the join is not needed.
You appearently have a duplicate join, not really a problem (probably) but not very nice.
And your where can be much easier ...

     Booking.Booking_start_date<[Please enter end date] 
and (Booking.Booking_end_date  >[Please enter start date] 
       or Booking.Booking_end_date  is null )

 

[helpme5]

Sorry, I meant that I need this query to show when there is a vehicle free for any certain day and at a specific time, e.g. 2nd February 2009 between 1am and 3pm.

Therefore vehicles that are booked between, say, 9am and 5pm will not appear when the search for the available vehicles would be carried out.

Thanks.

 

[namliam]

Turn around your where ... This where selects everything that is taken... I think... or not?