Access 2003: Need module to add dates to report.

[eckert1961]

I have a database that I use to track student's personal, grading and payment
information. For my student attendance I have a report that I print off at
the beginning of each month. On the report I have the student names down the
left side and the dates are text boxes which are across the top.

Example:

Student Name textbox1 textbox2 textbox3 textbox4 textbox5 etc

For the month of April the headings would look as follows:

Student Name 2 5 7 9 12 14......

This example is for a class that runs on Monday, Wednesday and Friday
evenings.

The first 3 dates on the report are taken from the 3 text boxes that are
directly below a calendar control on a form.. The remaining dates are
calculated from these 3 dates. Here is an example of how the date in the 4th
heading is calculated.

=IIf((IsNull(Forms![Attendance
Form]!ThirdDate)),Val([Field20])+7,DatePart("d",Forms![Attendance
Form]!ThirdDate))

The reason that I test if the Third Date is blank is because I have some
classes that are only run on Wednesday and Friday evenings.

This works but I'm thinking that it might be better to use a function that
would populate my report's column headings with the dates that it determines
for a given month for the noted weekdays. Additionally, it would be great if
I could input Statutory Holidays into the function so that those dates would
either not be entered into the report or possibly have that column colored
grey.

I can provide a sample database that only contains the attendance report.
Hopefully, this will give someone, who might be able to assist me with this,
an understanding of how the report is designed. Especially the column
headings that I want to populate with the required dates.

Any assistance would be greatly appreciated. Thanks.

 

[eckert1961]

Before I create a crosstab query, how would I generate the dates for the
Monday, Wednesday and Friday classes for a given month?

 

[John W. Vinson]

Before I create a crosstab query, how would I generate the dates for the
Monday, Wednesday and Friday classes for a given month?

For flexibility (you may add or change classes in the future, and there may be
holidays) I'd suggest creating a table of class dates. You can quickly
generate ten years or so of dates using Excel Insert... Fill Series, and copy
and paste the dates into an Access table which you can then edit.