Simple code?

[sithius]

So far I have a query that picks up any collisions between booking dates with the following:

SELECT [Customer Details].[CustomerID], [Booking].[RoomNumber], [Booking].[DateFrom], [Booking].[DateTo]
FROM [Room Details] INNER JOIN ([Customer Details] INNER JOIN Booking ON [Customer Details].[CustomerID]=[Booking].[CustomerID]) ON [Room Details].[RoomNo]=[Booking].[RoomNumber]
WHERE DateFrom Or DateTo Between [Forms]![Bookings]![DateFrom] And [Forms]![Bookings]![DateTo] And ([BookingID]<>[Forms]![Bookings]![text38]) And (RoomNumber=[Forms]![Bookings]!RoomNumber)
ORDER BY [DateFrom] And [DateTo];


But the problem is that it doesn't pick up dates inbetween DateTo and DateFrom. So:

Booking 1
12/12/06
20/12/06

Booking 2
13/12/06
19/12/06

On trying to book Booking 2 it will not notify me of 'no clashes' when there are. As it isn't passing by the 12th or 20th my SQL doesn't pick it up.

Can anybody help me? I'm new to code so I'm probably missing the obvious but all help will be greatly appreciated.

Cheers
Luke

 

[Dennisk]

overlapping bookings.
as a programmer I would write code to validate this kind of problem.

the technique I use is to use an array containing each pair of from and to dates and loop through the array testing if each date is between each pair of dates. If so then I have an overlapping booking.

 

[EMP]

Take a look at Jon K's post and sample (post #10) in this thread -

http://www.access-programmers.co.uk/forums/showthread.php?t=125130

In the sample, Jon used two simple queries to resolve an issue which is quite similar to the one you are facing.

^