You have an overlap between event A and event B if:
- A starts before B ends, AND
- B starts before A ends, AND
- it's the same employee, AND
- it's not the same event.
To achieve the 4th part, you will need a primary key in your table.
The example below assumes an AutoNumber primary key named ID.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If ((Me.EmpNo = Me.EmpNo.OldValue) AND _
(Me.StartDate = Me.StartDate.OldValue) AND _
(Me.EndDate = Me.EndDate.OldValue)) OR _
IsNull(Me.EmpNo) OR _
IsNull(Me.StartDate) OR _
IsNull(Me.EndDate) Then
'do nothing
Else
strWhere = "(EmpNo = " & Me.EmpNo & ") AND (StartDate <= " & _
Format(Me.EndDate, strcJetDate) & ") AND " & _
Format(Me.StartDate, strcJetDate) & _
" <= EndDate) AND (ID <" & Me.ID & ")"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clashes with event id " & varResult
End If
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Naushad" <nh*****@kockw.comwrote in message
news:b3**********************************@r36g2000 prf.googlegroups.com...
Hi All
Our Company is provided courses to employees for one or more days i.e.
there are three field (EmpNo, StartDate and EndDate) of course. I am
trying to display an Alert message when a perticular employee has to
attend a new course If he is busy in the other course between these
two dates.
Thanks in advance
Naushad
