"Sheet Total" #Error

[Tobin Atkinson, US Army Entertainment]

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.

 

[Allen Browne]

The Detail section of a form goes completely blank if both:
a) there are no records to display, and
b) no new record can be added.
In this case, there are no text boxes, and so attempting to sum them fails.
That's why the =Sum([xxx]) yields #Error.

To understand more about what causes that and workarounds, see:
Why does my form go completely blank?
at:
http://allenbrowne.com/casu-20.html

If that's not suitable, you may be able to replace the =Sumn([xxx]) with:
=IIf([Form].[Recordset].[RecordCount] > 0, Sum([xxx]), 0)
Unfortunately, this solution fails in Access 2007.
I suggest you take this approach:
Avoid #Error in form/report with no records
at:
http://allenbrowne.com/RecordCountError.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Tobin Atkinson, US Army Entertainment" <Tobin Atkinson, US Army
(e-mail address removed)> wrote in message

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.