How to fix formula to avoid divide/zero and aggregate issue.

  • Thread starter Thread starter Phil Smith
  • Start date Start date
P

Phil Smith

Here is the formula:

Expr1: Sum(CDbl(([Items on Selected Prebook-Type
Brand-Count]![SumOfSumOforder_qty]/[Items on Selected Prebook-Brand
Count]![SumOfSumOforder_qty])))

Occasionaly, the field to the right of the divisor is a null, generating
"Invalid use of null."

I tried using:
iff(isnull([Items on Selected Prebook-Brand
Count]![SumOfSumOforder_qty]),0,Sum(CDbl(([Items on Selected
Prebook-Type Brand-Count]![SumOfSumOforder_qty]/[Items on Selected
Prebook-Brand Count]![SumOfSumOforder_qty]))))

But I get "You tried to execute a query that does not include ...as part
of an aggregate.


So I changed it to

sum(iff(isnull([Items on Selected Prebook-Brand
Count]![SumOfSumOforder_qty]),0,Sum(CDbl(([Items on Selected
Prebook-Type Brand-Count]![SumOfSumOforder_qty]/[Items on Selected
Prebook-Brand Count]![SumOfSumOforder_qty])))))

And I get "Cannot have aggregate Function in expression...

In the words of Charlie Brown, "AAAUUUUUGH!"

It seems pretty simple. In the end, all I am trying to do is simple
division.
 
Try this:

SUM(IIF(isnull([Items on Selected Prebook-Brand
Count]![SumOfSumOforder_qty]),0,CDbl([Items on Selected
Prebook-Type Brand-Count]![SumOfSumOforder_qty])))/[Items on Selected
Prebook-Brand Count]![SumOfSumOforder_qty]
 
Test to see if [SumOfSumOforder_qty] is Null or Zero and
== if it is return zero or null or whatever other value you want
== if it is not then return the result of the calculation

Sum will handle either null or zero with no difficulty

CDbl could still error if
[Items on Selected Prebook-Type Brand-Count]![SumOfSumOforder_qty] is null.
So you might need to test for that also - depending on your data.

SUM(IIF(Nz([Items on Selected Prebook-BrandCount]![SumOfSumOforder_qty],0)=0
OR [Items on Selected Prebook-Type Brand-Count]![SumOfSumOforder_qty] is Null, 0,
CDbl([Items on Selected Prebook-Type Brand-Count]![SumOfSumOforder_qty]
/[Items on Selected Prebook-Brand Count]![SumOfSumOforder_qty]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Whoops! Unbalanced parentheses. Looks like I missed one at the end of the
expression.

SUM(IIF(Nz([Items on Selected Prebook-BrandCount]![SumOfSumOforder_qty],0)=0
OR [Items on Selected Prebook-Type Brand-Count]![SumOfSumOforder_qty] is Null, 0,
CDbl([Items on Selected Prebook-Type Brand-Count]![SumOfSumOforder_qty]
/[Items on Selected Prebook-Brand Count]![SumOfSumOforder_qty])))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John said:
Test to see if [SumOfSumOforder_qty] is Null or Zero and
== if it is return zero or null or whatever other value you want
== if it is not then return the result of the calculation

Sum will handle either null or zero with no difficulty

CDbl could still error if
[Items on Selected Prebook-Type Brand-Count]![SumOfSumOforder_qty] is
null. So you might need to test for that also - depending on your data.

SUM(IIF(Nz([Items on Selected
Prebook-BrandCount]![SumOfSumOforder_qty],0)=0 OR [Items on Selected
Prebook-Type Brand-Count]![SumOfSumOforder_qty] is Null, 0,
CDbl([Items on Selected Prebook-Type Brand-Count]![SumOfSumOforder_qty]
/[Items on Selected Prebook-Brand Count]![SumOfSumOforder_qty]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Try this:

SUM(IIF(isnull([Items on Selected Prebook-Brand
Count]![SumOfSumOforder_qty]),0,CDbl([Items on Selected
Prebook-Type Brand-Count]![SumOfSumOforder_qty])))/[Items on Selected
Prebook-Brand Count]![SumOfSumOforder_qty]
Click to expand...
Click to expand...
 
Never mind, lost something in translation. Redid to your specs, and it
appears to have worked well. Thanx.
 
Back
Top