I have a form with 6 subforms. I need to sum the total dollar amounts of all the subforms and post this value to a control on the main form. I'm using a button that does the computations (along with some other things).
This works fine when the subforms have values, but there will be many instances when at least one subform has no records. I get a "subform control has no value" error.
How can I get around this problem? I've tried the Nz() function in both the main and subforms, but it doesn't work. I was thinking of trying something in the subforms such as IIf(recordcount=0,0,sum(nz(DollarAmounts))) for the control with the total amount, but I don't know if that's possible, or if it's the smartest/fastest way to go about it.
Thanks, Brian, I learn something new every day....
However, it won't work for me. My problem is not that the fields have null values, but that there are no records in the subform. I need to either force the totals in the subform to zero when there are no records, or to check for records when summing at the main form level.
Thanks all, the IsNumeric() function worked great. I guess I was trying to make it a complicated problem.
Now for part 2-- When someone changes one of the subforms and I hit the Ccompute button, I have to hit it twice to get the calculation to work. It has to do with moving the focus back to the main form and saving the changes in the subform, I think. I'm requerying the subforms before doing the calculation and that fixes it, but is there a more intelligent way?