Excluding values in REPORT FOOTER CALC (min, max, avg)

[lolalo123]

Please, help! This is my first post here, and I am fairly new to Access.

I have a report that I inherit from another developer:

Report Header => Water Flow
Page Header (columns) => "Month", "Min Flow", "Max Flow", "Avg Flow"
DATESTAMP Header => empty
Detail => empty
DATESTAMP Footer => DATESTAMP, MIN(Flow), MAX(Flow), AVG(Flow)
Page Footer => empty

Report Footer ======>>>>> Here, for all 12 months, I would like to get the MIN, MAX, and AVG value of "Flow".

There's a catch, though! I would like the report to EXCLUDE AND GREY OUT the first 3 months for the MIN and MAX calculations here.

Is there a way to do so?

In the DATESTAMP Footer, I am able to make them invisible (for testing) using the On Format's Event Procedure. But I cannot seem to EXCLUDE the values in the MIN and MAX calculations in the Report Footer section.

I would like to reuse as much as what I have currently for this report (layout, query). If the current setup won't work, would there be another way to arrive at the same result?

Any help and suggestions would be greatly appreciated!

Thank you for your time and expertise!

 

[lameid]

Things to know:

1) Aggregate functions ignore nulls. If you have 5 records 2 containing nulls and 3 containing 1's, the average is 1 and not 3/5.

2) You can use the IIF funtion to conditionally display results in a query.

So if you added a column to the query behind the report that used the IIF funtion to display null for the first three months and Flow for the rest, you could use the functions over the new column in your report footer.

If you can tell me the criteria used to run the report to obtain the 12 months, I can help you with the IIF function.

 

[lolalo123]

Thank you, Not So "lameid"!

Thanks for reminding me of aggregate functions ignore nulls and the use of IIF function. I was able to do just that!

Sorry for a late reply! Deadline was looming and I had no time to inform you of your great tips.

Thank you, once again!!!