Please assist - Report showing #Num due to zero values

[rookie77]

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?

[rookie77]

Nevermind, As soon as I went back to it I guess my brain woke up and decided to function for me lol. I guess I was making it more complicated than necessary. But if you're here would you mind explaining the Nz function a little better for me? Maybe I misunderstood the purpose of that function.

[June7]

All Nz does is provide an alternate value if the field contains Null. Arithmetic with null returns null. If you want 0 as result, your expression could be:

InStock: Nz([SumOfInitQty], 0) / [SumOfQty]