Available dates query

[Schillers]

Hi guys,

Need help urgently as part of my university assignment. I have a database which is serving to act in place for a holiday park containing caravans. The problem is that within the search criteria I have to find times when caravans are free and bring up a list of available caravans. So you can only book a caravan if the start and end date are both before other times when it is booked or both after. I have this code:

SELECT TblCaravan.[Caravan No]
FROM TblCaravan INNER JOIN TblCaravanBooking ON TblCaravan.[Caravan No] = TblCaravanBooking.[Caravan No]
WHERE (((#[Forms]![Booking]![When Required]#)<[TblCaravanBooking].[When Required]))
ORDER BY TblCaravan.[Caravan No];

I cannot for the life of me work out how to make this work but as you can see the input data is being called from a field within a form.

Please help. I'm desperate!

Regards,
Schillers

 

[pbaldy]

Does this get you started?

http://www.baldyweb.com/OverLap.htm

 

[Schillers]

It kind of does. So I changed my code to:

SELECT TblCaravan.[Caravan No]
FROM TblCaravan INNER JOIN TblCaravanBooking ON TblCaravan.[Caravan No] = TblCaravanBooking.[Caravan No]
WHERE (((TblCaravanBooking.[When Required])>[Forms]![Booking].When_Required And (TblCaravanBooking.[When Required])>[Forms]![Booking].Required_Until)) OR (((TblCaravanBooking.[Required Until])<[Forms]![Booking].When_Required And (TblCaravanBooking.[Required Until])<[Forms]![Booking].Required_Until))
ORDER BY TblCaravan.[Caravan No];

Unfortunately, Access still brings up a dialogue box asking for me to input the data rather then just reading it from a form? :/

 

[pbaldy]

That doesn't look like the same technique. Is the form open when the query runs? If so, something must be misspelled (form or text box names).

 

[Schillers]

Thanks for all your help. I fixed it!

 

[pbaldy]

Happy to help.

 

[neilq5]

Thanks for all your help. I fixed it!

Hi Schillers, if you don't mind could you possibly show me the working code you used?
i need something very similiar for my database and i'm not great with SQL just yet

thanks!

 

[Schillers]

SELECT DISTINCT TblCaravan.[Caravan No]
FROM TblCaravan LEFT JOIN TblCaravanBooking ON TblCaravan.[Caravan No] = TblCaravanBooking.[Caravan No]
WHERE (((nz([When Required]>[Forms]![Booking].[Required_Until],True) Or nz([Required Until]<[Forms]![Booking].[When_Required],True))=True)