Re: Date range overlap

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

anonymous_at_discussions.microsoft.com
Date: 09/18/04 

Date: Sat, 18 Sep 2004 01:19:07 -0700

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.
>
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia.
>Tips for Access users - http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
>
>"jenny" <anonymous@discussions.microsoft.com> wrote in
message
>news:0f4101c49d11$4a5c14d0$a501280a@phx.gbl...
>>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
>
>
>.
>

Relevant Pages

  • Re: dynamic structure for storing/querying intervals
    ... intervals on a line (or, in possibly degenerate cases, just points). ... I'd like to be able to query the structure by providing a range (again, ... that when many intervals overlap the performance degrades significantly. ... Each node also contains the maximum endpoint ...
    (comp.programming)
  • Re: dynamic structure for storing/querying intervals
    ... intervals on a line (or, in possibly degenerate cases, just points). ... I'd like to be able to query the structure by providing a range (again, ... that when many intervals overlap the performance degrades significantly. ... Each node also contains the maximum endpoint ...
    (comp.programming)
  • Re: Availability between dates
    ... The interval does not overlap at all the interval [fromThis, ... make a second query which will look at all the items not in the first set. ... ID and belonging to a category, e.g. seats, chairs, tables, etc ... that will return which products can be hired based on the hire request ...
    (microsoft.public.access.queries)
  • Re: Parameter query with expressions and calculations
    ... So, to limit your query to those records where there are overlapping dates, ... Once you have the records there this is an overlap, ... I get the list of patients and the records ... CalcStartDate is...If the patient was on the vent before the start date ...
    (microsoft.public.access.queries)
  • Re: Parameter query with expressions and calculations
    ... So you want the number of days between ThpyStartDtTm and ThpyEndDtTm that overlap with the criteria range in your query. ... I get the list of patients and the records from this query and then export it to excel where I tally up the total days for all the patients as well as look up other info and add it to that sheet. ... My query pulls up patients from the NBICU who were on a vent (thpy ...
    (microsoft.public.access.queries)