Using our groups website database (in Access, exported to Alpha5), I have a report which displays somewhat like a graphic calendar: the report is for lectures given for 6 weeks, 3 days per week. All days have two lectures (displayed by time, speaker and lecture title); some days have a third lecture displayed.
The report is designed with each day's events designed in a sub-report. There is a calculated field of "Start" which equals the date of the first day of the 6-week term. The Eventdate for succeeding sub-reports is defined as Start+1, Start+2, etc. The template of the report is used for all three terms during an academic year. For each term, the "Start" date is redefined.
Each lecture is assigned to one of six categories. There is a designated field (Grp) for the categories, and the appropriate categories are displayed at the top of each day in the report, the two being separated by a slash.
My calculated field to display the two categories is, for example, Day1Categories= lookup("calendar.dbf","eventdate={01/18/2005}.and.milhr='0930'.and.eventtype='s'","alltrim(grp)")+" / "+lookup("calendar.dbf","eventdate={01/18/2005}.and.milhr='1100'.and.eventtype='s'","alltrim(grp)")
Here is my problem: Because the categories (Grp) display in the Group header of each sub-report, I need to define a calculated field for each day but want to use Start (or Start+1, etc.) so reports for succeeding terms need only to have the Start date redefined. If I specify the date (as above), the report picks up the Grp from the database and displays properly. However, I cannot seem to make this work when I substitute Start, Start+1 or the like in the expression. I am assuming there's something I don't know about using a calculated field inside another calculated field. I am obviously in need of expert help.
Thanks,
Sandy Nichols
The report is designed with each day's events designed in a sub-report. There is a calculated field of "Start" which equals the date of the first day of the 6-week term. The Eventdate for succeeding sub-reports is defined as Start+1, Start+2, etc. The template of the report is used for all three terms during an academic year. For each term, the "Start" date is redefined.
Each lecture is assigned to one of six categories. There is a designated field (Grp) for the categories, and the appropriate categories are displayed at the top of each day in the report, the two being separated by a slash.
My calculated field to display the two categories is, for example, Day1Categories= lookup("calendar.dbf","eventdate={01/18/2005}.and.milhr='0930'.and.eventtype='s'","alltrim(grp)")+" / "+lookup("calendar.dbf","eventdate={01/18/2005}.and.milhr='1100'.and.eventtype='s'","alltrim(grp)")
Here is my problem: Because the categories (Grp) display in the Group header of each sub-report, I need to define a calculated field for each day but want to use Start (or Start+1, etc.) so reports for succeeding terms need only to have the Start date redefined. If I specify the date (as above), the report picks up the Grp from the database and displays properly. However, I cannot seem to make this work when I substitute Start, Start+1 or the like in the expression. I am assuming there's something I don't know about using a calculated field inside another calculated field. I am obviously in need of expert help.
Thanks,
Sandy Nichols
Comment