Text Box display at report footer

  • Thread starter Thread starter Antonio`
  • Start date Start date
A

Antonio`

I have a report that displays attendance codes for
department personnel. When the report executes it prompts
for the employee id and then displays any dates and
associated attendance codes for that employee. This
report runs on the assumption that any date not on the
report indicates a regular day off or a normal worked day
for the employee. I would like to create a report footer
section that contains a group of individual text boxes
that display the attendance code when an entry exists for
a particular day, and are empty when no entry exists. But
I am having problems incerting and expression that will
allow for this.

Example:

Employee Group Section:
EmployeeID - BOB

Report Detail:
[Date] [AttendanceCode]
1/3/2003 T
1/6/2003 I

Report Footer:
txtjan1: " " (blank)
txtjan2: " " (blank)
txtjan3: T
txtjan4: " " (blank)
txtjan5: " " (blank)
txtjan6: I
etc...
The idea is to create a calendar made of several text
boxes that only display the attendance code if the txt
box expression specifies a record containing a specific
date. I thought I could use =IIf([Date]="1/1/2003",
[AttendanceCode], " ") for text box Jan1, =IIf([Date]
="1/2/2003",[AttendanceCode], " ") for text box Jan2,
etc...but it doesn't work and I really dont know what to
do. I would really really appreciate any suggestions or
help anyone could give...
 
I'm not entirely sure what you want but you may want to consider using
DLookup in your boxes. I must warn you though, it will run like a slug.

A more practical solution might be a multicolumn subreport (although they
are sods to set up!)
I wouldn't have thought that the Report footer was the place for either
though. I'd have thought both solutions need to be in the Group footer for
that Employee and, if his data is subdivided into months, into the Month
footer.

If you do go for the DLookup setup and you put the boxes in the Month footer
then you could have

=DLookUp("[AbsenceCode]","MyAttendanceTable", "[EmployeeID]="& [EmployeeID]
& " AND [DateFieldInMyTable] = " &
DateSerial(Year([DateFieldInMyReport]),Month([DateFieldInMyReport]),1)

That will get you the attendance code for the first of that month. Then you
would repeat that, replacing the last 1 with a 2 to get the second of that
month. (Obviously put it your real field and table names)

Since you are filtering the report before you open it, it might just be
feasible without taking too long to open.

Evi
 
Back
Top