Problem with Grouping Sum

[philcon]

Access2002/winxp

Hi all

I’ve produced a report based on a query and one of the calculated cfields in that query [charge] is in a currency format

The report has various groupings but when I put the field =sum([charge]) in any of the grouping footers, I get the error message “Data type mismatch in criteria expression”

If I remove the calculation from the group footer – no error message

Funnily I had a similar problem in another report, but all I did which fixed the problem was to delete and recreate all the relevant report controls. However in this instance that has not worked.

Does anyone have any idea what might cause this problem.

Many thanks in advance

 

[dhookom]

I rarely if ever format a column in a query. Most of the time, the formatting just gets in the way. When you view the query, does your calculation appear left or right aligned. If left aligned, it is being treated as a string and hence the issue with summing. You could either use
=Sum(Val([Charge]))
or use Val() around the calculation in the query (I prefer).

 

[philcon]

Thanks for replying dhookom,

Further investigation proved that in one of the records there was a division by zero.

For some reason this led to the error message instead of just showing Error in the appropriate field (as in the query)

Strange that this precluded the rest of the report from working.

Thanks once again


Phil.