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]

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" <de...@mailinator.com> wrote in message
news:65la70F...@mid.individual.net...

[CDMAP...@fortunejames.com]

In:

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

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

"For the solution shown above, the DateIntersection 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 DateIntersection(dt1, dt2, dt3, dt4) > 0.

James A. Fortune
CDMAP...@FortuneJames.com

[Albert D. Kallal]

To prevent collisions, the logic here is quite simple:

A collision occurs when:

RequestStartDate <= 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 dtRequeestStartDate as date
dim dtRequestEndDate as date

dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")

strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"

if dcount("*","tableBooking",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
pleaseNOO...@msn.com