showing available dates to book a room

[TheSmiddy]

so far i have set up a query which asks the user for a starting date and an ending date and then displays all the rooms that are booked inbetween those dates.

i then wanted another query to run off the first query which displays all the rooms that didnt show up in the first query. this query doesnt work no matter what i try.

Does anybody know what i can do or are there any suggestions for an alternative method of incorporating a feature like this?

 

[Norie]

What do you mean by did not show up?

Are you trying to get the rooms that were not booked?

Can you post the SQL?

To do so goto query design then View>SQL View and copy what is there.

 

[TheSmiddy]

yeah i was trying to get the rooms that are not booked in between two user selected dates

query for booked dated (working)

PARAMETERS [Please enter commencement date] DateTime, [Please enter ending date] DateTime;
SELECT rentals.PropertyID, rentals.[commencement date], rentals.[ending date]
FROM rentals
WHERE (((rentals.[commencement date]) Between [Please enter commencement date] And [Please enter ending date]-1)) OR (((rentals.[ending date]) Between [Please enter commencement date] And [Please enter ending date])) OR (((rentals.[commencement date])<[Please enter commencement date]) AND ((rentals.[ending date])>[Please enter ending date]-1));

query for available dates

SELECT [booked dates].PropertyID
FROM [booked dates]
WHERE ((([booked dates].PropertyID) Is Null));

 

[Norie]

Try this in a new query

PARAMETERS [Please enter commencement date] DateTime, [Please enter ending date] DateTime;
SELECT rentals.PropertyID, rentals.[commencement date], rentals.[ending date]
FROM rentals
WHERE (((rentals.[commencement date]) Not Between [Please enter commencement date] And [Please enter ending date]-1)) AND (((rentals.[ending date])Not Between [Please enter commencement date] And [Please enter ending date])) AND (((rentals.[commencement date])>=[Please enter commencement date]) OR ((rentals.[ending date])<=[Please enter ending date]-1));

Basically reversing your previous query

 

[TheSmiddy]

thanks, does this mean i can delete the booked dates query or is that still needed?

oh also, if a room has never been booked before it wont appear in the query, do i have to create a "fake" booking (ie book it from 1/1/1901 to 2/1/1901) or is there a way around it

 

[Norie]

If what I gave you works you can delete it I suppose.

But make sure that mine does work.