Using Nz function

[JoeA2006]

Is it possible to have instances after using the Nz function in an
expression, to still see a #Error in the text box of a form or report when
the field in the subform contains a null. Here is the expression which I
think is correct.

=Nz([qryPurchases subform].Form![PURCH TOTAL],0)+Nz([qryPurchasesW
subform].Form!PurchWTotal,0)

Since the underlying query returns no data in this instance I also tried
the IIf function, but that did not seem to work. The Nz function works fine
for the other calculations on the form.(from different subreports using
different queries)
and I am having trouble determining why this case is different.

 

[John Vinson]

I am having trouble determining why this case is different.

It's different because Access has a distinction between a record
containing a NULL valued field - and a record which simply does not
exist. There IS no record if the recordset is empty, so it's
meaningless - an #ERROR in fact - to try to determine the value or the
nullity of a field in that recordset.

You can use the Form's Error event to trap the error and take
appropriate action, if I recall aright.

John W. Vinson[MVP]