Highlighting Holidays

[s4]

Hi, I have two tables, one contains shift information and one contains
holidays for each person. I don't have an attendance table and could do
without having one. Can anyone help me with a way to highlight anyone who is
on holiday on my report?

The report is based on the rota table and lists each day of the week and who
is working. The holiday table contains the person's id, a start date and an
end date. So I need to check if each person on a given day's name crops up in
the holiday table where the date today(for that record) falls between start
and end in holidays.

Thanks for any help.

 

[Marc FERRER]

Hi, I have two tables, one contains shift information and one contains
holidays for each person. I don't have an attendance table and could do
without having one. Can anyone help me with a way to highlight anyone who
is
on holiday on my report?

The report is based on the rota table and lists each day of the week and
who
is working. The holiday table contains the person's id, a start date and
an
end date. So I need to check if each person on a given day's name crops up
in
the holiday table where the date today(for that record) falls between
start
and end in holidays.

Thanks for any help.

 

[Ken Sheridan]

Lets assume you have fields CurrentDate, Firstname, LastName and EmployeeID
in the report. Putting this in the detail section's Format event procedure
would show those employee's on holiday in red:

Dim strCriteria As String
Dim blnOnHol As Boolean

strCriteria = "#" & Format(Me.Currentdate, "mm/dd/yyyy") & _
"# Between StartDate And EndDate And EmployeeID = " & Me.EmployeeID

blnOnHol = Not IsNull(DLookup("EmployeeID", "Holidays", strCriteria))

If blnOnHol Then
Me.FirstName.ForeColor = vbRed
Me.LastName.ForeColor = vbRed
Else
Me.FirstName.ForeColor = vbBlack
Me.LastName.ForeColor = vbBlack
End If

where Holidays is the name of the table with the employee's holiday dates in
columns StartDate and EndDate.

Note that the report must have controls bound to the CurrentDate and
EmployeeID fields as, unlike in a form, code in a report's module can’t
directly reference fields in the underlying recordset. If you don't wish to
show the EmployeeID set its Visible property to False.

Ken Sheridan
Stafford, England

 

[s4]

Thanks Ken, that may be just what I'm looking for!
You;re a genius!!