For my computing project last year I created a simple booking system in Access 2003 with a little bit of VBA to enhance it a little. However this year I need to improve it.

One of the main problems I found with my booking system is that double bookings can be made. An example:

Room One
First booking:
Arrival: 12/06/08
Departure: 16/06/08
Second booking:
Arrival: 12/06/08
Departure: 16/06/08

With my current system those two bookings could be made in the same room as I've got no checking for it. Sure I could check to see if the date of arrival equals the date of arrival of another booking. But wait, here's another example:

Room One
First booking:
Arrival: 12/06/08
Departure: 16/06/08
Second booking:
Arrival: 10/06/08
Departure: 17/06/08

That's still a double booking but the second booking would fall through and still be made as if I was to check if date of arrival equals a previous date of arrival it wouldn't find anything as the dates are different but they still overlap.

I have two ideas of which I know how to do neither code wise.

Firstly I could give a physical imagery representation of the bookings. I've found a screenshot of a current booking system showing exactly my idea: image example basically the system will show in some form of a table when each room is booked at certain dates. To make a booking the user can just double click on an empty date and make the booking. However I have absolutely no idea how to do this table in VB.net (my language of choice, if it can be done in anything else then please do say and maybe I can find a work around).

My second idea would be to use just plain code. But I have no idea how to do this either. How do I check whether bookings overlap? If you have any ideas please say.

Sorry for the large block of text but eh. My education rests on finding a solution. I'm not asking anyone to just give me the code and tell me how to do it exactly, but please help me find the right information that I need.

Thanks in advance.

What you need to do is the following:

1. Run a query to see if any bookings fall within your date range.
(Select * from Bookings where BookDate >= YourStartDate or BookDate <=YourEndDate

2. If there is a record returned, you cannot book.

3. Next, check to see if there is a departing record
(Select * from Bookings where Departdate > YourStartDate AND DepartDate =<YourEndDate

4. If a record is returned, someone is departing durign that time. You cannot book.

Make sense?

Mhmm. I see exactly where you're coming from ericstenson and it would work wonderfully. However what would happen if a booking was made before the booked date and they departed after it? For example:

First booking
Arrival: 17/04/09
Departure: 25/04/09
Second booking
Arrival: 16/04/09
Departure: 27/04/09

I guess it complicates matters further and it could be an oversight because it's only a school project, I just would like some form of way to stop double bookings for both my project and for personal reasons as I'd like to create future programs. Thanks once again, I'll look further into it and if anyone else has more information it'd be further appreciated thanks.

In my example above BookDate means Arrival Date, sorry on the confusion

Using your example above, the following reservation has been made:

A: 4/17/2009
D: 4/25/2009.

Now, a customer calls and says I want to book 04/16/09 to 04/27/09.

Let's go through my steps.

1. Do any arrivals fall within the date range?
Arrival of 4/17/2009 falls between 4/16 and 4/27 -- record is returned

2. If there is a record returned, you cannot book.
We cannot book this the 2nd reservation.

****

Let's modify the example, and say your 2nd resevation was 4/18/09 to 4/27/09.

1. Do any arrivals fall within the range?
Previous booking has arrival of 4/17, so NO.

2. If so, you cannot continue.
We can continue, because the answer was NO.

3. Are any departures within the date range of 4/18/09 to 4/27/09?
Yes, the first booking is leaving 04/25/09.

4. If so, you cannot continue.
We cannot make the reservation, because a deperature falls within the range.


Does this make sense??

It will take me time to read through it over and over it again and to code it to see the results but it's getting much clearer and I am truely thankful for your help as I can now try and get this booking system created with no double bookings. Thank you so much indeed!