I have a report that is returning percentages based upon other calculated fields. Some of the percentage fields are showing #Num on the report. I've found this is due to one of the calculated values being zero.
3 Fields used:
Field: Qty
Total: Sum
Field: InitQty
Total: Sum
Field: OrderQty: Sum([Qty]-[InitQty])
Total: Expression
I need two more fields to return percentages based off of those 3 fields. This is what I started out with:
Field: InStock: [SumOfInitQty]/[SumOfQty]
Total: Expression
Field: Ordered: [OrderQty]/[SumOfQty]
Total: Expression
This works great until the query returns one that has sum of qty equal to zero.
So this is what I've tried:
Field: InStock: IIf(Nz([SumOfInitQty],0),0,[SumOfInitQty]/[SumOfQty])
Total: Expression
Seems like it should work but its still showing #Num when I run the query.
Can someone help me figure this out?