Trying to make summary figures for report, not working

[kagoodwin13]

I am trying to include computed averages in the header of a report for a quick summary view. This average needs to ignore zero values so that they do not weigh down the average incorrectly. I have done this before in my database, but now that I am trying to generate another report with different data, the formula I used previously is not working.

Previously, I had this code to calculate the average Cost Per Unit (CPU) given CPU and its annual volume, ignoring CPU values in records that have zero volume:

=Sum(IIf([A_CPU]=0,Null,([A_CPU]*[A Volume])))/Sum(IIf([A_CPU]=0,Null,[A Volume]))

This code works. The report runs, and generates an average figure.

Now, I am trying to use the same formula to calculate average cost per kilometer (for trucking) when given a rate and distance, ignoring rates in records with zero kilometers:

=Sum(IIf([KM]=0,Null,([Rate])))/Sum(IIf([KM]=0,Null,([KM])))

When I run this formula though, I get an error message: "The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numerical expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

I'm not sure why the second formula, which is essentially the same as the first one, is too complex. Is there something I've missed?

[RuralGuy]

Is there some reason you enclosed the last [KM] in parens? "([KM])" I know it should not make any difference, just curious.

[kagoodwin13]

I just did a replacement of field names. I tried without parentheses, but I still get the same error.

[RuralGuy]

Sometimes Access can get persistant errors. Try deleting the control, Compact and Repair, then open and add a new control with the same ControlSource.

[kagoodwin13]

Found the problem: the data type of [KM] was text, not number. D'oh!

Thanks for your help.

[RuralGuy]

Outstanding. Thanks for posting back with your success.