Need running total to display zero if null

[skwilliams]

I have a form (frmCS) and a subform within it (frmUpselling). This subform has a control source of (qryUpselling). The subform displays records from (qryUpselling) based on each employee along with a total at the bottom of the subform. This is an unbound textbox (Text4) in the subform footer with the control source of "=Sum(Nz([Amt],0))". This all works fine unless the query doesn't contain data for that person. The total at the bottom then displays nothing. When there is no data for that person the subform itself doesn't show anything but the control headers and the empty textbox.

Attached are some screenshots.

Any ideas??

 

[rohnds]

If the sub form doesn't contain record matching the criteria (link) from the main form, nothing is displayed. This is how sub and main forms works. And this is how you want it to work.

 

[SteveSchapel]

Skwilliams,

Try it like this:

=IIf(IsNumeric(Sum(Nz([Amt],0))),Sum(Nz([Amt],0)),0)

 

[skwilliams]

I tried =IIf(IsNumeric(Sum(Nz([Amt],0))),Sum(Nz([Amt],0)),0)
It still doesn't display anything.

The total in the footer is used to fill a control in the main employee form (frmCS!OlUs)which is used in other calculations. If the total control on the subform won't produce a zero value for no records then I at least need the control on the main form to display a zero because that is the final goal.

Thanks.