Need module to add dates to report.

[eckert1961]

Hello,

This is actually a continuation of the following post. I thought that it might be cleaner if I started a new post since the original one is several months old.

http://www.access-programmers.co.uk/...d.php?t=157053

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. The zip file contains a sample attendance report from one of my classes and a screenshot from my Attendance Form that I open this report from.

The first 3 dates on the report are taken from the 3 text boxes that are directly below the calendar. 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. The attached report is for a class that is run on MWF.

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.

Any thoughts on what would be a good approach?

Thanks and regards,
Chris

 

[eckert1961]

Would anyone have any pointers?

Chris

 

[DCrake]

Are you using a cross tab query as the underlying query for the report or not?

If not how is you table structured?

 

[eckert1961]

Hello DCrake,

Thank you for you reply.

Are you using a cross tab query as the underlying query for the report or not?

If not how is you table structured?

I am using a select query. Here is the SQL view.

SELECT DISTINCTROW Members.LastName, Members.FirstName, ClassTypes.ClassName, Members.Active
FROM ClassTypes RIGHT JOIN Members ON ClassTypes.ClassTypeID = Members.ClassTypeID
WHERE (((Members.Active)=Yes))
ORDER BY Members.LastName;

Please let me know if you need any additional information.

Regards,
Chris

 

[eckert1961]

I've attached a sample database that only contains the attendance report. Hopefully, this will give everyone, 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.

Please let me know if you require anything else.

Thanks in advance.

Chris

 

[eckert1961]

I've spent quite a bit of time searching this forum and others to see if something similar has already been done and so far I've found nothing. Then again I'm not a VBA programmer so I may have stumbled across it and not have recognized it. I have found similar posts in this forum but none of them have a resolution.

Although I have a working report I have to believe that what I'm attempting to do is possible. Would anyone have some time to assist me with this? Even a pointer to a link would be appreciated.

Thanks and regards,
Chris

 

[eckert1961]

I'm surprised that no one has responded. Any suggestion on what information I need to provide to have this move forward?

Chris