I have a form with a number of subforms.

On the main form, there is a control to adds up the subtotals on each of the subforms.

Sometimes, the underlying query for a subform my have no results. This is ok and on purpose.

The problem is, though, that I get #Error instead of a total on the main form.

I'm assuming it's because the subtotal on the subform control is blank, or null, or empty, or whatever the correct terminology is.

How can I force this control to be 0 when the query returns no records, so that it doesn't mess up my other controls?

Thank you

Hi,

"Blank" and "empty" are not very useful terms in databases because they tend to be ambiguous.

Terms that ARE useful include "Null" and "Zero Length String" (although that latter can take some thought to understand too).

There is an excellent discussion of Nulls here.

The solution should be to incorporate the NZ() function in your expression to convert nulls to zeros.

That's what I was thinking, but I can't seem to get it to work.

In the footer of my subform I have:
=Sum(nz([totalcost]))
and it works fine as long the the query for the subforms as something there. If there are no items I can't get it to display a 0, it's just empty. (As in the control is empty to the eyes.)

I've also tried
=nz([frmWorkOrderPartsMaterialSubform].[Form]![MaterialTotal])
on the main form, but if there's nothing showing in the control in the subform footer I just get #error

In that case, you can use the IIf() function to return the 0 when there are no records in the subform's recordset.

=IIf(IsNull([frmWorkOrderPartsMaterialSubform].[Form]![MaterialTotal]),0, [frmWorkOrderPartsMaterialSubform].[Form]![MaterialTotal])