detect overlapping time periods

[Guest]

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.

 

[Marshall Barton]

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?

Two ranges overlap if the following condition is true:

start1 < end2 AND end1 > start2

 

[Alex Dybenko]

here a function i use:

Public Function IsOverlapping(ByVal Per1Beg As Date, _
ByVal Per1End As Date, _
ByVal Per2Beg As Date, _
ByVal Per2End As Date) As Boolean

If Per1Beg <= Per2Beg And Per1End <= Per2Beg Then Exit Function
If Per1Beg >= Per2End Then Exit Function
IsOverlapping = True

End Function

 

[Guest]

Thanks.

I know what the criteria are to compare two periods, but how do I check that
the period on the form does not overlap with ANY of the multiple periods
defined in the table?

 

[Guest]

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

 

[Guest]

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.

 

[Alex Dybenko]

just use this function in a query WHERE, per1 is dates on your form, Per2 -
fields from your table

 

[Marshall Barton]

I know what the criteria are to compare two periods, but how do I check that
the period on the form does not overlap with ANY of the multiple periods
defined in the table?

In VBA??

You can us
If DCount("*", "table","the criteria expression") > 0 Then
' something overlaps
Else
' it's ok
End If