Nz Formula Help Needed

[liamrowan]

Access 2003
Report Formula

Here is a formula that returns what I need as long as the fields being added have at least one field with a value greater than or equal to zero:

=Sum([Paid])/Sum([Approved])
​

This returns the sum of the Paid column divided by the sum of the Approved column.

However, occasionally ALL the values being summed in either the Paid or Approved columns are empty cells, and the return is a #Num error. I have tried to use the Nz function to make the forumula return an empty field in such cases:

=Sum(Nz([Paid]))/Sum(Nz([Approved]))
​

But I still get the same error. Is this fixable?

Thanks, William

 

[boblarson]

You have to ensure you aren't trying to divide by zero.

=IIF(Sum(Nz([Approved]))=0,0,Sum(Nz([Paid]))/Sum(Nz([Approved])))

You'll have to determine what you want if it is zero and trying to divide by zero. I just have it doing 0 right now as the actual value returned for the entire formula if the sum of approved is 0.

​

 

[boblarson]

Oh, I forgot to mention - make sure that there are no controls on the report named APPROVED and PAID. If there are, change them to txtApproved and txtPaid and then make sure to still use the FIELD name of [Approved] and [Paid] in the formulas. If you haven't turned off Name AutoCorrect (also known as autoCORRUPT) you will have to go back and change the formulas back to using the field name and not the ones with txt appended to them.

 

[Rick_A]

I'm pretty sure you'll also need to call the Nz function like so:

Nz([Approved],0)

 

[boblarson]

I'm pretty sure you'll also need to call the Nz function like so:

Nz([Approved],0)

I was going to put that in too, but decided against it as I was sure it was optional (which it is UNLESS it is in a QUERY).

Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.

Note If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.

 

[liamrowan]

Thanks for all the help; everything is working perfectly!

Wm