Displaying #Error as 0 1

[TTThio]

I have a subform which is linked to a combo box in the main form. This subform is pulling data from the underlying query.
For some selection in the combo box, the subform will return no data (shows dimmed subform), which is correct, because there's no data match.
However, I also have a textbox in the main form displaying a value of a field in the subform, exp. txt1 control source = subform1![field1].
As the selection returns no data, the text box displays #error.
Yet, I need it (textbox.value) to display/return 0 even there's no data return in the subform itself because the textbox.value is a part of a calculation field.

Anybody knows how it works?
Thanks ahead!

 

[JerryDennison]

You might try using the nz() function:

= nz(subform1![field1])

If this doesn't work you might try this instead:

= IIF(IsError(subform1![field1]), 0, subform1![field1])

 

[TTThio]

Thanks Jerry.

I've tried with NZ before it didn't work. And neither with IsError this time.
I thinks because these functions value is a reference to a cell value/formula. When no data returned, the whole subform is dimmed, look like disactivated. That's why there's no reference can be made.

I'm kind of okay now, what I'm doing is format the subform for allow additions, so that even no data is matched, the subform will display blank lines. Then I just use IsNull function for the related textbox.

Yet, unluckily, this method is not applicable in the report.
When no data returned, the subreport is not diplayed, so that the calculation field in the main report cannot perform the right calculation, just as what happened to the form before.

See if you have any other idea.... Thanks!!