Addition on the Main Form from two subforms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a main Form which contains two subforms and I want to total the
amounts entered in text boxes in these two forms on the main form.

So in order to achieve the reqd. result I put a text box on my Main form and
added the two, Text49 and CA_2007 by using the following expression
=(c_PM_QtySum_subform.Form!Text49)+(d_PM_CA_Entry_subform.Form!CA_2007)

But the problem I run into is if either of these (Text49 and CA_2007) field
on the subform have a null value i.e. nothing even though the other might
have an amount my Total shows up as empty.

Example:
Text49 = empty
CA_2007 = 5000
Total = empty

Is there a way to show the value in my Total even if one of the subform
fields is empty.
 
Use the Null to Zero function

=Nz(c_PM_QtySum_subform.Form!Text49,0)+Nz(d_PM_CA_Entry_subform.Form!CA_2007,0)
 
Use Nz() to indicate you want a zero for Null, i.e.:
=Nz(c_PM_QtySum_subform.Form!Text49, 0) +
Nz(d_PM_CA_Entry_subform.Form!CA_2007, 0)

That should work unless there are no records in the subform and none can be
added, in which case it goes completely blank and referring to the
non-existent text box generates an error.
 
Thanks for the advice I think Dennis had the same expression. But I think you
might have hit the nail on the head as:

"That should work unless there are no records in the subform and none can be
added, in which case it goes completely blank and referring to the
non-existent text box generates an error."

I think that is what is happening, is there a way to get over this given if
either of the subform field are completely blank?

Thanks
MSA
 
This kind of thing should do it:

=IIf([c_PM_QtySum_subform].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz(c_PM_QtySum_subform.Form!Text49, 0)) + ...
 
Back
Top