How to obtain part of a date range

[Deano]

The short version;
In short, given one date range (start and end dates) how can I find the
period that overlaps with another date range?

The long version;
I have knocked up a little application that helps my friend monitor employee
absences.

You can enter the start and end dates of an absence. For reports the user
specifies start and end dates which produces a list of people with absences
in that period. Only problem is if someone's absence overlaps into the
report period.

e.g report covers March 1st to April 1st but won't include an absence that
started on Jan 1st and finished on March 20th. In that case I need to pick
up that overlapping absence which would be March 1st to March 20th.

Looking at it now I can't understand how I missed something so obvious but I
can't work out how to revise my queries.

Any suggestions?

[Randy Shore]

Re: How to obtain part of a date range

What you want is [StartDate]<= [EndRange] AND [EndDate] >=[StartRange].
That will pick up your 1/1 - 3/20 absence, but what about absences that are
unfinished? If the report covered Feb 1 - Mar 1 your 1/1 - 3/20 wouldn't
meet the criteria, so you need to add a second criterion: [StartDate] <=
[EndRange] AND [EndDate] Is Null.

"Deano" <deano@mailinat or.comwrote in message
news:65la70F2e2 e87U1@mid.indiv idual.net...

The short version;
In short, given one date range (start and end dates) how can I find the
period that overlaps with another date range?
>
The long version;
I have knocked up a little application that helps my friend monitor
employee
absences.
>
You can enter the start and end dates of an absence. For reports the user
specifies start and end dates which produces a list of people with
absences
in that period. Only problem is if someone's absence overlaps into the
report period.
>
e.g report covers March 1st to April 1st but won't include an absence that
started on Jan 1st and finished on March 20th. In that case I need to
pick
up that overlapping absence which would be March 1st to March 20th.
>
Looking at it now I can't understand how I missed something so obvious but
I
can't work out how to revise my queries.
>
Any suggestions?
>
>

[CDMAPoster@fortunejames.com]

Re: How to obtain part of a date range

On Apr 3, 8:14 pm, "Deano" <de...@mailinat or.comwrote:

The short version;
In short, given one date range (start and end dates) how can I find the
period that overlaps with another date range?
>
The long version;
I have knocked up a little application that helps my friend monitor employee
absences.
>
You can enter the start and end dates of an absence.  For reports the user
specifies start and end dates which produces a list of people with absences
in that period.  Only problem is if someone's absence overlaps into the
report period.
>
e.g report covers March 1st to April 1st but won't include an absence that
started on Jan 1st and finished on March 20th.  In that case I need to pick
up that overlapping absence which would be March 1st to March 20th.
>
Looking at it now I can't understand how I missed something so obvious butI
can't work out how to revise my queries.
>
Any suggestions?

In:

http://groups.google.com/group/comp.databases.ms-access/msg/bffe893be4b75102

I gave a function called DateIntersectio n that calculates the number
of days of overlap between two date ranges. In that post I also said:

"For the solution shown above, the DateIntersectio n function can be
replaced by an appropriate SQL expression for greater range of
applicability."

What I meant by that was that the function was intended to be used as
a tool to get something working right away. The function allows the
range logic to be encapsulated while the basic problem logic is
pondered as a way of controlling the complexity. Once the basic
problem logic is working I usually replace the function by a SQL
statement so that the answer is dependent only on SQL rather than on
SQL in conjunction with a User Defined Function (UDF).

Two date ranges overlap if DateIntersectio n(dt1, dt2, dt3, dt4) 0.

James A. Fortune
CDMAPoster@Fort uneJames.com

[Albert D. Kallal]

Re: How to obtain part of a date range

To prevent collisions, the logic here is quite simple:


A collision occurs when:


RequestStartDat e <= EndDate
and
RequestEndDate >= StartDate


The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStart Date as date
dim dtRequestEndDat e as date


dtRequestStartD ate = inputbox("Enter start Date")
dtRequestEndDat e = inputbox("Enter end date")


strWhere="#" & format(dtReques tStartDate,"mm/dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtReques tEndDate,"mm/dd/yyyy") & "# >= StartDate"


if dcount("*","tab leBooking",strW here) 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. However, what is nice here is that
the simple conditions above does return ANY collisions....


--

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com