LEARN MS-ACCESS TIPS AND TRICKS

Ms-Access Functions Sum, Count, Avg etc. cannot be used in Page Header or Footer Areas of Reports for creating Page-wise Summary Values. These will work in Report Group Header/Footer or in Report Header/Footer areas only.

You don't have to design two different Reports; one for Detail Listing of records with Group-wise Totals and another one for Group-wise Totals alone. We can play a small trick to get both outputs from the same Report depending on the User's choice.

Recommended reading before proceeding with this topic:

Hiding Report Lines Conditionally
Hiding Records and Group Footer Calculations
Hiding Group Header/Footer and Detail Sections

For hiding of Report Lines conditionally there are other methods too. For example the following VBA Code (instead of the earlier simple method we have tried) can give you the same result for hiding Detail Section Report Lines:

This is the continuation of two Articles published earlier on this Subject. You may go through them by following the links given below before continuing from here.

1. Hiding Report Lines Conditionally
2. Hiding Report Lines Conditionally-2

We have learned how to hide Report items conditionally and calculate Sub-Totals and Report Footer Totals in Code by excluding values of those records we have removed from the Report Detail Section. We have updated the calculated values in Group Footer and Report Footer Section Controls.

Last week, we have started the topic of hiding Report Detail Section data lines and worked through a simple example as well. But, we have not yet seen how this action affects the normal calculations on the Report, like calculating Group-wise Sub-Totals or Report Footer Totals.

Hiding Report lines or Report Sections conditionally at run time may not be an every day requirement. But, it is interesting to know how to do it, if it becomes necessary, with the help of Programs. One thing I like very much about MS-Access Report is its ability to write programs on the Report itself, especially for highlighting very critical information on the Report so that the attention of Users can be drawn to specific information on it. You can see an interesting and useful example in one of the earlier Posts with the Title: Highlighting Reports.