Joe, I am confused about where the problem lies: on the report, or on a
form. If it is a report with a subreport, the article will solve the
problem. If it is a form with a subform, it will not help.
From your example, it looks like TextTTL is a text box in a subform. Since
you say it contains a total, I will assume that TextTTL is in the Form
Footer section of your subform (not page footer, or detail section.) I will
also assume that it contains a numeric calculation. To help ensure that
Access understands the data type, set its Format property to General Number
or Currency of something numeric.
Now, the text box on the main form bound to:
=[qryNR subform1].[Form].[TextTTL]
works fine when there are records. Set its Format property as well.
When there are no records in the subform, one of 2 things happens:
a) the subform shows a new record, or
b) the Detail section of the subform goes competely blank.
(b) happens if new records cannot be added to the subform (e.g. because its
source is read-only, or because its AllowAdditions property is set to No.)
If your case is (a), the value of the text box is Null, and Nz() solves it.
If your case is (b), referring to the non-existent control in the subform
results in an error. To avoid the error, you need to use IIf() to handle the
zero-recordcount as a special case, so the Control Source would be:
=IIf([qryNR subform1].[Form].[RecordsetClone].[RecordCount]=0, 0,
Nz([qryNR subform1].[Form].[TextTTL],))
There is another factor: the Name of the subform control may not be the same
as the name of the form it contains. To check its name, open the main form
in design view, right-click the edge of the subform control, and choose
Properties. On the Other tab of the Properties box you see the Name of the
control. Its SourceObject property indicates what form gets loaded into the
control.
If it is in fact a report with a subreport, using HasData instead of testing
the RecordCount of the RecordsetClone achieves the same result.
If you are still stuck, you can debug it by opening the Immediate Window
(Ctrl+G) while the form is open, and enter something like this:
? Forms![Form1]![qryNR subform1].[Form].[TextTTL]
By taking a bit at a time, you may be able to identify which part of the
name is wrong.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
JoeA2006 said:
If I test this with data
=[qryNR subform1].[Form].[TextTTL]
I get a total in the Text box
When I test with IIf function with data I get
#Name?
=IIf([qryNR subform1].[Form].[HasData], [qryNR subform1].[Form].[TextTTL],
0)
What is different?
Allen Browne said:
See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
The article explains how to test the HasData property of the subreport,
and
why that's necessary.
I need to use a total from a subreport that may or may not have data for
a
calculation. The Nz and IIf functions seem to work when the subform
show
up
on the main form, however one form does not show up and when I use the
IIf
or
Nz function I still get an error. Why do other objects show and this
one
does
not?The calculation seems to work on the accompanying report, but will
not
work on the main form.
