dates between

[Bibi]

I have a reservation db. It works very well EXCEPT that I do not know how to
create a query that will show which properties are available on a certain
date or range of dates.
tblTenants, tblLeases, tblProperties, and tblDatesReserved. - tblLeases has
arrive date and depart date.
I have created a subform for the main registration form that I use to
enter the date of each night reserved into tblDatesReserved. I do not know
how to show that each date between the arrive date and the depart date (-1)
is reserved for the property leased and that a date/property combo that is
not reserved is AVAILABLE. I can not write code .
I do have a pivot table that illustrates availability by blanks but I
would really like to be able to use a query.

 

[Allen Browne]

Use a subquery to identify whether a record is available on a certain date.

1. Create a query using just your Properties table.

2. Type an expression like this into the Field row in query design.
It all goes on one line, and will be something like this:

NOT EXISTS (SELECT PropertyID FROM tblDatesReserved
WHERE tblDatesReserved.PropertyID = tblProperties.PropertyID
AND #10/24/2007# Between tblDatesReserved.[arrive date]
And tblDatesReserved.[depart date] - 1)

Once you have that working, you can replace the literal date with a text box
on a form if you wish. This kind of thing:
AND [Forms].[Form1].[Text0] Between ...

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Bibi]

Thank you very much - it worked! and I was able to create a form. I had
never heard of subqueries--

Bibi

Use a subquery to identify whether a record is available on a certain date.

1. Create a query using just your Properties table.

2. Type an expression like this into the Field row in query design.
It all goes on one line, and will be something like this:

NOT EXISTS (SELECT PropertyID FROM tblDatesReserved
WHERE tblDatesReserved.PropertyID = tblProperties.PropertyID
AND #10/24/2007# Between tblDatesReserved.[arrive date]
And tblDatesReserved.[depart date] - 1)

Once you have that working, you can replace the literal date with a text box
on a form if you wish. This kind of thing:
AND [Forms].[Form1].[Text0] Between ...

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html