I’m having a problem with a “total” box in a Call Sheet (request for payment)
form using Access 2003.  The total is a number (currency).

I have ten subforms in the form that link to the term “Call Sheet #” titled:

“line 1 BPA data subform” through "line 10 BPA data subform"

All the subform data appears PERFECTLY in the form.

Each subform has a “total”.  In the footer of each subform I have created a
hidden formula

“=sum([total])”

These formulas are named “line1sum” thru “line10sum”.

Back in the main form I then made formula (hidden) for each subform:

=Nz([line 1 BPA data subform]!line1sum)

and named this formula “Line 1 Total” thru “Line 10 Total”.

Then I have created a final formula in a separate (visible) box called
“Sheet Total”

=[line 1 total] + [line 2 total] + [line 3 total] + [line 4 total] + [line 5
total] + [line 6 total] + [line 7 total] + [line 8 total] + [line 9 total] +
[line 1 total]

The problem is, if any of the lines don’t have data, then I get a “#ERROR”.  
If I delete any of the empty boxes from the “Sheet Total” formula, for
instance just:

=[line 1 total] + [line 2 total] + [line 3 total] + [line 4 total] + [line 5
total] + [line 6 total]

then everything is perfect.  The proper amount is shown.  If I add [line 7
total] which is void of data, then I get a “#ERROR”.

I’ve tried to fix the hidden formulas on the main form with:

=Nz([line 7 BPA data subform]![line7sum])
=Nz([line 7 BPA data subform]!line7sum,0)
=Nz([line 7 BPA data subform]![line7sum],0)

I’ve even tried putting the “Sheet Total” formula in the footer of the main
form, but none of the Nz tactics I’ve found on the web or in my Access 2003
Bible seem to work.  

So:  how can I get either the hidden formula  “=Nz([line 1 BPA data
subform]!line1sum)” or “=Nz([line 1 BPA data subform]!line1sum,0) to produce
a “0” amount if no data appears OR what can I do in “Sheet Total” to get a
“0” if [line 7 total] is empty?

Thanks.