Check if a date is between 2 dates in a subform

[Sandpiper]

Hi

I'm trying to create a booking form which prevents you double booking the material.
I have a booking form (frmBookings) where you enter the [ProductID], [StartDate] of loan and [EndDate]. (tblBookings)

I'm also displaying a subform sfrmBookings which shows all future bookings for the same product. (qryBookingsSubform).

When I enter the startdate in the bookings form, I want it to display a message if the product is already booked.

I've tried an If statement, and do until, but i've got a feeling i'm making things too complicated, and it's not working.

If anyone can help, i'd be grateful.

 

[jzwp22]

When I enter the startdate in the bookings form, I want it to display a message if the product is already booked.

Since a booking encompasses a time frame, just checking the start date relative to existing records is not enough. For example, the start date could occur before the start date of any existing records, but then if the new record's end date falls in between an existing record's start and end date you would still not be able to make the booking. Another more interesting case is if the start and end dates of an existing booking fall completely with the begining and ending dates of the proposed new record's date. So, the start and end dates will have be tested for all of the following conditions

1. Proposed record start date falls between the start and end dates of existing records
2. Proposed record end date falls between the start and end dates of existing records
3. Proposed record start and end dates brackets the start and end dates of existing records.

So I would set up a query that checks existing records and see if any of the conditions are met. If no records are returned then you can book the new record, otherwise you cannot.

You will have to reference the two form controls that contain the proposed start and end dates. Here is what the query would look like. In the WHERE clause are the 3 conditions mentioned above

WHERE (condition1) OR (condition2) OR (condition3)

SELECT pkbookingID
FROM tblBooking
WHERE (FORMS!FRMBOOKING!PROPOSEDSTART Between startdate And enddate) OR (FORMS!FRMBOOKING!PROPOSEDEND Between startdate And enddate) OR (enddate<=FORMS!FRMBOOKING!PROPOSEDEND And startdate>=FORMS!FRMBOOKING!PROPOSEDSTART);

You can actually simplify the WHERE clause and still accomplish handling the 3 conditions by using this query:

SELECT pkbookingID
FROM tblBooking
WHERE (FORMS!FRMBOOKING!PROPOSEDEND>=startdate And FORMS!FRMBOOKING!PROPOSEDSTART<=enddate);