How to find conflict between time

[Jon]

Greeting,

If I have 3 employees and I do not want them to be in vacation at the same
time. For example, if employee1 vacation is started on 02/22/2009 and end in
02/28/2009. Employee2 vacation is started on 02/27/2009 and end in 03/3/2009.
Finally employee3 vacation is started on 02/26/2009 and end in 03/03/2009.
Therefore, emp1 & emp2 are conflicted in time of their vacation by 1 day and
so on with emp3. What I want to do is how to prevent vacation from
conflicting by showing the different between vacations??

 

[Douglas J. Steele]

See whether what Allen Browne has at
http://www.allenbrowne.com/appevent.html helps.

 

[ken]

Lets assume you have a table Vacations with columns EmployeeID,
StartDate and EndDate then in the BeforeUpdate event procedure of a
form bound to the table you should be able to check for conflicts
with:


Const conMESSAGE = "Dates conflict with an existing vacation."
Dim strCriteria As String

strCriteria = "#" & Format(Me.StartDate, "yyyy-mm-dd") & _
"# Between EndDate And StartDate Or " & _
"#" & Format(Me.EndDate, "yyyy-mm-dd") & _
"# Between EndDate And StartDate Or " & _
"StartDate Between #" & _
Format(Me.StartDate, "yyyy-mm-dd") & "# And #" & _
Format(Me.EndDate, "yyyy-mm-dd") & "# Or " & _
"EndDate Between #" & _
Format(Me.StartDate, "yyyy-mm-dd") & "# And #" & _
Format(Me.EndDate, "yyyy-mm-dd") & "#"

If Not IsNull(DLookup("EmployeeID", "Vacations", strCriteria))
Then
MsgBox conMESSAGE, vbIExclamation, "Invalid Operation"
Cancel = True
End If


If you simply want to warn the user rather than disallow the insertion
or update of the record, then remove the 'Cancel = True' line. The
record will then be inserted/updated, but the user warned of the
conflict.

If you want to see the conflicting vacation records then rather than
simply informing the user you could use the MsgBox function to ask
them if they wish to view the conflicting vacation records, and if so
open another form with:

DoCmd.OpenForm "frmConflictingVacations", _
WhereCondition:=strCriteria, _
WindowMode:= acDialog

Ken Sheridan
Stafford, England