Sum of Expression Fields HELP

[Guest]

I have a report that is based on a query. In my query, I have a field that
is an if expression. The expression reads, IIf(IsNull([Initial]),"
",[UnitsProcessed]). I put the expression in a field not based on a table
called ProcessedUnits (Same thing basically as UnitsProcessed, I just kept
getting a circular reference error when it was called UnitsProcessed). I put
this in because I only wanted my query returning results of units the
operators at our company had processed (they put their initials by every
product that is processed.) So, if the unit has not been processed, no
initials are by it, and it does not return with the query. This works
perfectly.
Problem: My problem is when I generate a report for this. I wish to have
the ProcessedUnits (field with expression)on my report, and this does show
up. I then wish to sum up these ProcessedUnits to have a total at the bottom
of report. This doesn't work. It keeps saying the formula is too complex.
I tried adding text boxes w/Sum([ProcessedUnits)] control source to no avail.
What do I need to do to sum up these values???

 

[Dirk Goldgar]

I have a report that is based on a query. In my query, I have a
field that is an if expression. The expression reads,
IIf(IsNull([Initial])," ",[UnitsProcessed]). I put the expression in
a field not based on a table called ProcessedUnits (Same thing
basically as UnitsProcessed, I just kept getting a circular reference
error when it was called UnitsProcessed). I put this in because I
only wanted my query returning results of units the operators at our
company had processed (they put their initials by every product that
is processed.) So, if the unit has not been processed, no initials
are by it, and it does not return with the query. This works
perfectly.
Problem: My problem is when I generate a report for this. I wish to
have the ProcessedUnits (field with expression)on my report, and this
does show up. I then wish to sum up these ProcessedUnits to have a
total at the bottom of report. This doesn't work. It keeps saying
the formula is too complex. I tried adding text boxes
w/Sum([ProcessedUnits)] control source to no avail. What do I need
to do to sum up these values???

Sometimes you get that message, not because the query expression is
really too complex, but because the expression can't be evaluated due to
an error. In this case, I suspect that your calculated field is causing
an error because it returns a string value -- " " -- in some cases, and
you can't sum string values. Try changing your calculated field to one
of the followiing; either this:

IIf(IsNull([Initial]), Null, [UnitsProcessed])

or this:

IIf(IsNull([Initial]), 0, [UnitsProcessed])

If I'm right, you should then be able to put a text box on your report
with the controlsource

=Sum([ProcessedUnits])