RE: detect overlapping time periods
From: Peter Martin (PeterMartin_at_discussions.microsoft.com)
Date: 09/16/04
- Next message: Greg: "Re: Default Value"
- Previous message: Douglas J. Steele: "Re: Defining a Database"
- In reply to: Peter Martin: "RE: detect overlapping time periods"
- Next in thread: Marshall Barton: "Re: detect overlapping time periods"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 16 Sep 2004 09:19:01 -0700
Allen Brown MVP has a nice explanation of a simpler method (using only a
query) at
http://members.iinet.net.au/~allenbrowne/appevent.html
but he uses a cartesian product which /may/ not be appropriate for your
application.
Regards, Peter.
"Peter Martin" wrote:
> HI,
>
> I have this function MultiCover which may be useful. You supply it a
> recordset made up of the start and
> end times of the intervals as separate records. You could make this with a
> union query.
>
> SELECT dtmStartTime as dbl, 1 as Ev FROM mytimes UNION SELECT dtmEndTime as
> dbl, -1 as Ev FROM mytimes ORDER BY dbl
>
> Then, if there was any overlap between your form and the database records
> you'd have
> MultiCover(rs, Form!myform!dtstart, Form!myform!dtEnd)>0.
>
> nMin, nMax are just min and max functions, something like
> Public Function Min(ParamArray la() As Variant) As Variant
> Dim l As Long
> If UBound(la) = 0 Then Min = Null: Exit Function
> Min = la(0)
> For l = 1 To UBound(la)
> Min = IIf(la(l) < Min, la(l), Min)
> Next l
> End Function
>
> Note it doesn't set rs=nothing because I used the rs elsewhere - its
> probably better for you to define the rs inside the function and pass SQL in
> your case.
>
>
> Public Function MultiCover(rs As DAO.Recordset, Optional ClipLo, Optional
> ClipHi) As Double
> 'Finds cover of set of intervals between optional clipping
> 'rs: set of period start/stop events format !dbl (ASC),!Ev {1,-1)
>
> Dim dblStart As Double, lStarts As Long, dblClipLo As Double, dblClipHi As
> Double
>
> dblClipLo = IIf(IsMissing(ClipLo), DBL_MIN, CDbl(ClipLo))
> dblClipHi = IIf(IsMissing(ClipHi), DBL_MAX, CDbl(ClipHi))
> If (IsMissing(ClipLo) Or IsMissing(ClipHi)) Then
> Else
> If dblClipHi < dblClipLo Then dblStart = dblClipHi: dblClipHi =
> dblClipLo: dblClipLo = dblStart 'Swap
> End If
>
> With rs
> If Not .BOF Then .MoveFirst
> Do While Not .EOF
> If !ev > 0 Then
> If lStarts = 0 Then dblStart = nMin(nMax(!dbl, dblClipLo), dblClipHi)
> lStarts = lStarts + 1
> Else
> lStarts = lStarts - 1
> If lStarts = 0 Then MultiCover = MultiCover + nMax(nMin(!dbl,
> dblClipHi), dblClipLo) - dblStart
> End If
> .MoveNext
> Loop
> End With
>
> End Function
>
> Well its one idea maybe wait for something better from the MVP's.
>
> Regards
>
> "mikebo" wrote:
>
> > I need to detect if two time periods overlap. I have multiple time periods
> > defined in a table with a start date and an end date. On a form, another
> > start and end date is defined. I need to find out if the period defined on
> > the form overlaps with any of the periods defined int he table. How do I do
> > that?
> >
> > I thought I had posted this before, but I can't see it, so here it is again.
> >
> >
- Next message: Greg: "Re: Default Value"
- Previous message: Douglas J. Steele: "Re: Defining a Database"
- In reply to: Peter Martin: "RE: detect overlapping time periods"
- Next in thread: Marshall Barton: "Re: detect overlapping time periods"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
