query with dates, ...help pls!

[rew]

Hi,

i want to create a booking system but i've a problem

i've these tables


cars

_ car_id
_ car_name


booking

_ book_id
_ book_start
_ book_end
_ car_id_fk



and the following data:

car 1: booked from 11.01.2007 until 14.01.2007
car 1: booked from 19.01.2007 until 24.01.2007
car 2: booked from 02.01.2007 until 28.01.2007
car 3: booked from 25.01.2007 until 29.01.2007
car 4: no booking

how can i make if i want that a query show the available cars
for the period (example): 12.01.2007 - 16.01.2007 ??

'cause i've found something and it'll show me car 3 and car 1 available..but the problem is that car 1 is still out on the 12th of january..(cause this query will show only the cars already booked in other periods that don't interfere with the request).

How can i make a query that shows the cars available and NOT those periods??

thx in advance

 

[raskew]

Hi -

Take a look at Allen Browne's 'Clashing Events' solution: http://allenbrowne.com/appevent.html

HTH - Bob

 

[EMP]

What you need is a series of two queries.

qryBooked:-
SELECT [Car_ID_fk]
FROM [Booking]
WHERE [Book_End]>=[Enter Start Date] And [Book_Start]<=[Enter End Date];

qryAvailable:-
SELECT [Cars].[Car_ID], [Cars].[Car_Name]
FROM [Cars] LEFT JOIN [qryBooked] ON [Cars].[Car_ID]=[qryBooked].[Car_ID_fk]
WHERE [qryBooked].[Car_ID_fk] Is Null;


Run the second query "qryAvailable" and enter the start date and end date parameters when asked.

If you use two text boxes (e.g. txtFrom and txtTo) on a form for the user to enter the start date and end date, you can reference the text boxes in the Where Clause of the first query like this:-

WHERE [Book_End]>=[forms]![formName]![txtFrom] And [Book_Start]<=[forms]![formName]![txtTo];

^

 

[rew]

EMP thx very much!! it works!

and thx to raskew, very useful website!!