detect overlapping time periods

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
mikebo said:
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?
Click to expand...


Two ranges overlap if the following condition is true:

start1 < end2 AND end1 > start2
 
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
 
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?
 
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
 
just use this function in a query WHERE, per1 is dates on your form, Per2 -
fields from your table
 
mikebo said:
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?
Click to expand...

In VBA??

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