Access Report Footer Error When Summing Data Column

[HardingR2000]

I have a column of numeric values which I want to sum on the group footer, which works, and on the report footer, which does not work.

When the object, =sum([WTS_Volume]), is included in the Report Footer the report goes through the report's "On No Data" event and gives me the message I placed in that event saying that there is no data.

If I move the sum object to the Page Footer the report is produced, there are data values in the detail section for the column being summed, the Group Footer displays the currect summed value, but the Page Footer summed value shows "#Error".

Thinking that I have some hidden problem in the report I have recreated it twice from scratch without success.

Does anyone know why the =sum(...) object is not working?

 

[lameid]

Are you using the field in the detail section of the report too? Likely there is or would be an instance where it returns #Error as well.

When you get that from an aggregate function is usually means you have tried to sum a calculated field or expression that results in an error...


Sum(Invoice_Total / Invoice_Qty)

In the example above, Invoice_Qty could be 0 and would result in a division by zero error (different display than #Error with out the aggregate but I expect aggregates return #Error).

More often I have seen this where a userdefined function that errors out is used.

More rare is data corruption where the value in the table actually says #Error for a field. The only way to fix it is to delete the record and reenter it. The problem here is in finding the record as where clauses have a funny way of keeping these records out of the results.

Hopefully you are using a calculated field in a query... Otherwise start looking for #Error in the table.

 

[HardingR2000]

I am using the field [WTS_Volume] in the detail section and I am summarizing it in a Group Footer section. It is in the Page Footer and Report Footer where the =sum([WTS_Volume]) is not working.

I guess I should have also told everyone that the record source is a stored procedure and I had a filter specified on the report. The filter works fine for the detail section but the summary value is wrong in the Group Footer section and gives an error in the Page Footer and Report Footer sections. When I removed the filter all the Footer sections return the correct values. It is only with the filter that there are problems.

To solve my problem so that I could move on to other things, I added a parameter to the stored procedure to allow the filtering to occur in the stored procedure instead of in the Access report. That, of course, necessitated that I change all places where I used the stored procedure to ensure I passed the appropriate parameter. This solved my problem, however I am still interested in why having a filter caused the sum values to be incorrect or in error in the report.

Thanks.

 

[lameid]

Ah that makes sense. If you changed your stored procedure to a view I expect it would have worked too. However I believe your performance will be better with the stored procedure as Acces will sometimes do a table scan in this fashion against Sql then filter it on the client which kind of defeates the point of Client-Server environments.

The issue is that when you run a report it executes the query multiple times to perform grouping etc. Within Access this can cause queries that will run on their own to be 'too complex' when used for a report. Clearly Access is hiccuping requesting the same data multiple times.

 

[HardingR2000]

The result set from the stored procedure is very small (rows and columns) and with a filter (either via SQL stored procedure parameter or the report filter the final result set to be used is even smaller.

I would think that there has to be an Access problem (bug). However, I am not going to purse this further. It is working now and hopefully I will remember this if it should happen again.

Thanks for your help.

 

[lameid]

I would definitely call it a bug... I only say that it makes a little sense knowing that reports execute queries multiple times. It is odd though that it doesn't completely bomb the report like the similar issue I mentioned.

I agree that it is not worth more time... file it under some things are easier to fix than to understand.