SQL query confusion

[Maldini]

Hi,

I'm working on a venue booking system right now for my local club which loans out its grounds and courts to people.

What I'm trying to produce is a query that will check whether a court is being booked. On this form, the user will input the start and final dates for which they wish to book this venue. I the need to check if this overlaps with any current bookings for that venue.

Problem is that I have four cases to handle, when the new booking begins before an existing, starts before the end of an existing, is "inside" the days of an existing and is "longer" that that of an existing.

So I want to make a query to return any bookings that satisfy the above, then I'll know that this new booking cannot be made.

SELECT Facility.ID, Booking.Start_Date, Booking.End_Date
FROM Booking INNER JOIN Facility_booked ON Booking.Booking_number=Facility_booked.Booking_num
WHERE (((#[Booking.Start_Date]# < #[Forms!Booking!Start_Date]#) AND (#[Booking.End_Date]# > #[Forms!Booking!End_Date]#)) OR ((#[Booking.Start_Date]# > #[Forms!Booking!Start_Date]#) AND (#[Booking.End_Date]# < #[Forms!Booking!End_Date]#)) OR ((#[Booking.Start_Date]# > #[Forms!Booking!Start_Date]#) AND (#[Booking.End_Date]# < #[Forms!Booking!End_Date]#)) OR ((#[Booking.Start_Date]# < #[Forms!Booking!Start_Date]#) AND (#[Booking.End_Date]# > #[Forms!Booking!End_Date]#)));

where Start_Date and End_Date are textboxes on my form... but now I keep getting errors.... anyone know why?

Thx.
Maldini

 

[JoyInOK]

Is this SQL in VB code or is it going in the Query Design window?
Is the datatype of the Start_Date and End_Date in the table date?

 

[Maldini]

well, I'm using this from access so I was making the query in the SQL view since I couldn't seem to do what I wanted in the table view...

yes, the datatype of start and end date are both date in the tables, in the format of dd/mm/yyyy

 

[JoyInOK]

If it's in the query builder's SQL window, I can tell you that you'll need to convert the text in your textboxes on the form to dates:
cDate(Forms!Booking.[Start_Date]) and drop the #'s

If it's in code, you'll need to concantonate the string
...WHERE #&quot; & Forms!Booking.[Start_Date] & &quot;# < ...

 

[Maldini]

Thanks, that seems to be the case.
Going to re-do the SQL since its becoming a pigs ear to edit.

Thanks again.
Maldini

 

[johnnymac43]

Maldini,

In your post you say: Problem is that I have four cases to handle, when the new booking begins before an existing, starts before the end of an existing, is &quot;inside&quot; the days of an existing and is &quot;longer&quot; that that of an existing.

This rather muddled thinking is reflected in your SQL. If you restate the above as:

1. If the proposed start date or the proposed end date is between the existing start and end dates the booking is rejected.
2. If the proposed start date is prior to the existing start date and the proposed end date is not prior to the existing start date the booking is rejected.

then you have only two cases to handle, and can write your SQL on that basis.

HTH

John