event clash

[Joe]

I use Allen Browne's sample clash query
(http://members.iinet.net.au/~allenbrowne/appevent.html)
to check new absence records before they are updated. The
absence records are deemed to overlap if any date
overlaps. Record 1 does not show a record a clash. But
record 2 doe's record a clash.
Record 1
eventname start end
locationid eventid Recorded By
absent 13/09/2004 15/09/2004 Peter
Barwell 8 Joe
absent 15/09/2004 19/09/2004 Peter
Barwell 36 Joe
Record 2
eventname start end
locationid eventid Recorded By
absent 13/09/2004 15/09/2004 Peter
Barwell 8 Joe
absent 14/09/2004 19/09/2004 Peter
Barwell 36 Joe

This is not quite what I want as both records are
considered overlaps. Is it possible to refine the query to
show both records as clashes.

Allen Browne's query
SELECT tEvent.eventid, tEvent.start, tEvent.end,
tEvent.locationid, tEvent_1.eventid, tEvent_1.start,
tEvent_1.end, tEvent_1.locationid
FROM tEvent, tEvent AS tEvent_1
WHERE (((Nz(([tEvent_1].[Start]>=[tEvent].[End]) Or
([tEvent_1].[End]<=[tEvent].[Start]) Or ([tEvent].
[LocationID]<>[tEvent_1].[LocationID]) Or ([tEvent].
[EventID]=[tEvent_1].[EventID]),False))=False))
ORDER BY tEvent.eventid, tEvent_1.eventid;

 

[Allen Browne]

Hi Joe

If you only want to check whether any existing records clash with the new
one, you don't need to check every record against every other record. Just
look for any record where:
- it starts before this one ends, and
- this one also starts before that one ends, and
- it is a different record (not the same EventID).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

strWhere = "(Start < " & Format(Me.End, "\#mm\/dd\/yyyy\#") & _
") AND (" & Format(Me.Start, "\#mm\/dd\/yyyy\#") & _
" < [End]) AND (EventID <> " & Me.EventID & ")"
varResult = DLookup("EventID", "EventTable", strWhere)

If Not IsNull(varResult) Then
strMsg = "Clash with event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If

Hope I've understood you correctly here.

 

[Guest]

Hi Allen
Thanks for your response, sorry I have not got back until
now(). I was able to take your suggestion and tweak it
into the right shape.
Once again many thanks
Joe

-----Original Message-----
Hi Joe

If you only want to check whether any existing records clash with the new
one, you don't need to check every record against every other record. Just
look for any record where:
- it starts before this one ends, and
- this one also starts before that one ends, and
- it is a different record (not the same EventID).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

strWhere = "(Start < " & Format

(Me.End, "\#mm\/dd\/yyyy\#") & _

") AND (" & Format(Me.Start, "\#mm\/dd\/yyyy\#") & _
" < [End]) AND (EventID <> " & Me.EventID & ")"
varResult = DLookup("EventID", "EventTable", strWhere)

If Not IsNull(varResult) Then
strMsg = "Clash with event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If

Hope I've understood you correctly here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I use Allen Browne's sample clash query
(http://members.iinet.net.au/~allenbrowne/appevent.html)
to check new absence records before they are updated. The
absence records are deemed to overlap if any date
overlaps. Record 1 does not show a record a clash. But
record 2 doe's record a clash.
Record 1
eventname start end
locationid eventid Recorded By
absent 13/09/2004 15/09/2004 Peter
Barwell 8 Joe
absent 15/09/2004 19/09/2004 Peter
Barwell 36 Joe
Record 2
eventname start end
locationid eventid Recorded By
absent 13/09/2004 15/09/2004 Peter
Barwell 8 Joe
absent 14/09/2004 19/09/2004 Peter
Barwell 36 Joe

This is not quite what I want as both records are
considered overlaps. Is it possible to refine the query to
show both records as clashes.

Allen Browne's query
SELECT tEvent.eventid, tEvent.start, tEvent.end,
tEvent.locationid, tEvent_1.eventid, tEvent_1.start,
tEvent_1.end, tEvent_1.locationid
FROM tEvent, tEvent AS tEvent_1
WHERE (((Nz(([tEvent_1].[Start]>=[tEvent].[End]) Or
([tEvent_1].[End]<=[tEvent].[Start]) Or ([tEvent].
[LocationID]<>[tEvent_1].[LocationID]) Or ([tEvent].
[EventID]=[tEvent_1].[EventID]),False))=False))
ORDER BY tEvent.eventid, tEvent_1.eventid;

.