Date range overlap

[jenny]

I need to know if a date range overlaps another date range
if the datepart is a weekday for example:

Date range 1 = 16/08/2004 to 22/08/2004

Date Range 2 = 20/08/2004 to 21/08/2004 = true
Date Range 2 = 12/08/2004 to 12/08/2004 = false
Date Range 2 = 21/08/2004 to 22/08/2004 = false
Date Range 2 = 20/08/2004 to 22/08/2004 = true
Date Range 2 = 12/08/2004 to 16/08/2004 = true

If anyone knows a quick way to evaluate the answer. I
would appreciate your help.

Thanks Jenny

 

[Allen Browne]

There are 2 parts to your question:
1. Does it overlap?
2. Is the overlap a weekday?

Two events overlap if both:
- A starts before B ends, and
- B starts before A ends.
You can create a query that identifies all the overlaps by using those
criteria. Detailed example (including handling Null dates) in article:
Clashing Events/Appointments
at:
http://members.iinet.net.au/~allenbrowne/appevent.html

Once you have that query returning all the records that have overlapping
dates, you then need to discover whether any of those overlapping days are
Monday-Friday. That means you need to convert the ranges to records to you
can examine each day. You can either loop through them all in code, and loop
through the days in each range, examining the Weekday() of each one.
Alternatively, you can create a table of holidays and weekends, and use
another cartesian product between this table and the query above, limiting
the dates to each range.

 

[Guest]

Hello Allen
Thank you for your pointer to your web page and your
article. This will provide excellent support for my
project. I have a question I hope you can answer! I
intended to provide the date range I am comparing the
tables records with via two calendar controls on an
unbound form. I could write a temporary record to the
table to provide the query with the data then delete this
after I have my information (This works) but it seems a
little messy. Is it possible to compare external date
ranges using an unbound form control? I have also removed
the second record in the query's results by placing a "Not
Is Null" parameter in the [event_1].[locationid]. This
again appears to work, but will it have any bearing on the
way the query works.

Once again thanks for your support
Jenny

 

[Allen Browne]

Comparing to a pair of dates is much easier, because you do not need to
compare every record against every other record to find the overlaps.

You could do that just by creating a query into your table, and using
Criteria under your date fields, e.g.
StartDate EndDate
<= [Forms]![Form1].[EndDate] >= [Forms]![Form1].[StartDate]

If both date fields in your table are required (i.e. they cannot be null),
and both date controls on the form have values, then you can safely omit the
bit about Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen
Thank you for your pointer to your web page and your
article. This will provide excellent support for my
project. I have a question I hope you can answer! I
intended to provide the date range I am comparing the
tables records with via two calendar controls on an
unbound form. I could write a temporary record to the
table to provide the query with the data then delete this
after I have my information (This works) but it seems a
little messy. Is it possible to compare external date
ranges using an unbound form control? I have also removed
the second record in the query's results by placing a "Not
Is Null" parameter in the [event_1].[locationid]. This
again appears to work, but will it have any bearing on the
way the query works.

Once again thanks for your support
Jenny

-----Original Message-----
There are 2 parts to your question:
1. Does it overlap?
2. Is the overlap a weekday?

Two events overlap if both:
- A starts before B ends, and
- B starts before A ends.
You can create a query that identifies all the overlaps by using those
criteria. Detailed example (including handling Null dates) in article:
Clashing Events/Appointments
at:
http://members.iinet.net.au/~allenbrowne/appevent.html

Once you have that query returning all the records that have overlapping
dates, you then need to discover whether any of those overlapping days are
Monday-Friday. That means you need to convert the ranges to records to you
can examine each day. You can either loop through them all in code, and loop
through the days in each range, examining the Weekday() of each one.
Alternatively, you can create a table of holidays and weekends, and use
another cartesian product between this table and the query above, limiting
the dates to each range.

 

[Guest]

Perfect Thank you
Jenny

-----Original Message-----
Comparing to a pair of dates is much easier, because you do not need to
compare every record against every other record to find the overlaps.

You could do that just by creating a query into your table, and using
Criteria under your date fields, e.g.
StartDate EndDate
<= [Forms]![Form1].[EndDate] >= [Forms]![Form1]. [StartDate]

If both date fields in your table are required (i.e. they cannot be null),
and both date controls on the form have values, then you can safely omit the
bit about Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen
Thank you for your pointer to your web page and your
article. This will provide excellent support for my
project. I have a question I hope you can answer! I
intended to provide the date range I am comparing the
tables records with via two calendar controls on an
unbound form. I could write a temporary record to the
table to provide the query with the data then delete this
after I have my information (This works) but it seems a
little messy. Is it possible to compare external date
ranges using an unbound form control? I have also removed
the second record in the query's results by placing a "Not
Is Null" parameter in the [event_1].[locationid]. This
again appears to work, but will it have any bearing on the
way the query works.

Once again thanks for your support
Jenny

-----Original Message-----
There are 2 parts to your question:
1. Does it overlap?
2. Is the overlap a weekday?

Two events overlap if both:
- A starts before B ends, and
- B starts before A ends.
You can create a query that identifies all the overlaps by using those
criteria. Detailed example (including handling Null dates) in article:
Clashing Events/Appointments
at:
http://members.iinet.net.au/~allenbrowne/appevent.html

Once you have that query returning all the records that have overlapping
dates, you then need to discover whether any of those overlapping days are
Monday-Friday. That means you need to convert the ranges to records to you
can examine each day. You can either loop through them all in code, and loop
through the days in each range, examining the Weekday() of each one.
Alternatively, you can create a table of holidays and weekends, and use
another cartesian product between this table and the query above, limiting
the dates to each range.

I need to know if a date range overlaps another date range
if the datepart is a weekday for example:

Date range 1 = 16/08/2004 to 22/08/2004

Date Range 2 = 20/08/2004 to 21/08/2004 = true
Date Range 2 = 12/08/2004 to 12/08/2004 = false
Date Range 2 = 21/08/2004 to 22/08/2004 = false
Date Range 2 = 20/08/2004 to 22/08/2004 = true
Date Range 2 = 12/08/2004 to 16/08/2004 = true

If anyone knows a quick way to evaluate the answer. I
would appreciate your help.

Thanks Jenny

.