# Please assist - Report showing #Num due to zero values

1. Novice
Windows 7 32bit Access 2010 32bit
Join Date
Nov 2012
Posts
4

## Please assist - Report showing #Num due to zero values

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?

2. Novice
Windows 7 32bit Access 2010 32bit
Join Date
Nov 2012
Posts
4
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.

3. 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]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums - Senior Forums