Selecting date ranges in a query

[ethall]

I'm trying to set up a query that pulls all date ranges that intersect with a
user-provided date range.

So, if I'm scheduled to be on vacation between 1 Jan and 1 Feb, those dates
are stored as start/end dates in a vacation table. If a user wants to know
who is on vacation between 10 Jan and 20 Jan, I won't show. How do I get the
query to include the time I'm on vacation when the start and end dates are
outside the query?

 

[Allen Browne]

2 ranges overlap if:
A starts before B ends, and
B starts before A ends.

To verify the logic, draw pairs of lines on a piece of paper to represent
the 2 ranges, e.g.:
--------------------

 

[Douglas J. Steele]

Your Where condition would be something along the lines of:

WHERE VacationStartDate <= #2009-01-10#
AND VacationEndDate >= #2009-01-20#

 

[Douglas J. Steele]

Sorry, that's incorrect.

It should be

WHERE VacationStartDate <= #2009-01-20#
AND VacationEndDate >= #2009-01-10#

In other words, you want to know those vacations that start before the end
of the period and end after the start of the period.

Check what Allen Browne has at http://www.allenbrowne.com/appevent.html for
a more detailed explanation of why.