I'm sorry, I obviously didn't completely understand all your questions, I am
still very new at this. Here is another try:
For the Subreport, your are correct I don't really understand the
difference. I'm not sure where to find the name of the control for the
subreport. I deleted the original label that popped up with the sub-report.
Under the properties the Name is listed as
"rptORRMonthlyBirthsCountSubreport". Both this sub-report and the textbox
with the equation are listed in a group footer. If that doesn't answer it
can you tell me where I can find the control name? I am sorry I don't know
exactly.
I didn't have any data in the database to run in the sub-report, so "0" is
what I expected, however I now have some sample data in it and when I put
=IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData,
rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag, 0)
in the text box it returns "#Error", I would expect to see a total sum of "1".
I also tried =rptORRMonthlyBirthsCountSubreport.Report.HasData again and
that returns "-1". I would expect the total to be "1", therefore positive
and not negative.
The control name of my text box "SumOfNumberOfHead" was incorrect, I
re-named it and I also tried =Nz(SumOfNumberOfHead,0) again, this time it
returned -177, which is not quite what I would expect, there are two records
shown for the text box "SumOfNumberOfHead", -177 and -1, I would expect it to
sum those and therefore report -178. If I enter =Nz(SumOfNumberOfHead,0) in
on a month with no records it returns "0" which I would expect.
It looks like the problem is with the sub-report.
I am sorry it is taking me so long to figure this out, but thank you for
your patience and help!
--
Thorson
Duane Hookom said:
I didn't ask what the name of the subreport was. I wanted the name of the
control on the main report that contains the subreport. This might be the
name of the subreport but could be anything. Your answer doesn't tell me you
specifically understand the difference.
Apparently your subreport is not returning any records. When you replied :
=====================
If I enter =IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData,
rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag, 0) in the text box
it results as "0"
=====================
I would have expected you to include something like "this is what I expect
since the subreport doesn't have any records returned". I also expect you
would run the report with a criteria that does actually return records in the
subreport. You can't really test an expression when there aren't expected
results.
Does your subreport return only a single record? If not, why would you want
to return a value from the detail section of the subreport rather than an
aggregate value from the subreport footer?
You stated SumOfNumberOfHead is the name of a text box and the name of a
field. This could be true but your statement:
====================
If I enter =Nz(SumOfNumberOfHead,0) in the text box it results as #Error
====================
suggests that either it isn't a numeric field in the record source or the
name of the text box containing this control source is possibly a field name.
Have you turned off all Name-Autocorrect options in your database?
--
Duane Hookom
Microsoft Access MVP
:
It didn't work.
Yes, the name of the sub-report is [rptORRMonthlyBirthsCountSubreport]
No the text box is not in the same section as the sub-report. The text box
[SumOfNumberOfHead] has more than one record and I want it to display all of
them, however if the subreport is in the same section it repeats as well and
I don't want that.
No, The sub-report's text-box [CountOfEarTag] Is in the Detail section of
the Sub-report, I could move it to the report footer, I shouldn't make any
difference as far as how the report looks.
Yes, [SumOfNumberOfHead] is a field in he main report's Record Source.
"NumberOfHead" is the field and the "total" line is set to Sum, which results
in the field name as [SumOfNumberOfHead]
If I enter =Nz(SumOfNumberOfHead,0) in the text box it results as #Error
If I enter =IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData,
rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag, 0) in the text box
it results as "0"
If I enter =rptORRMonthlyBirthsCountSubreport.Report.HasData in the text box
it results as "0"
This may be completely unrelated, if it is I can work on it at another time:
I am also worried that my changing the name of the field in the query and
therefore in the report may have messed something up. Along with changing
the name of the field "SumOfExpr4" to "SumOfNumberOfHead" I also changed the
name of 2 other fields, "Expr3" and "Expr4", even though I also changed them
in the reports and subreports it is asking me for those parameters ("Expr3"
and "Expr4"). I checked my object dependencies and made sure everything
related was changed as well, but It is still asking for those parameters.
--
Thorson
:
Try:
=Nz(SumOfNumberOfHead,0)+IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData, rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag, 0)
If that doesn't help, please confirm the following:
The name of the subreport control on the main report (not necessarily the
Source Document) is [rptORRMonthlyBirthsCountSubreport]
This text box is in the same section of the main report as the subreport.
The subreport has a text box in its Report Footer section named CountOfEarTag.
SumOfNumberOfHead is a field in your main report's record source.
What displays in a text box with this Control Source:
=Nz(SumOfNumberOfHead,0)
What displays in a text box with this Control Source:
=IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData,
rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag, 0)
What displays in a text box with this Control Source:
=rptORRMonthlyBirthsCountSubreport.Report.HasData
--
Duane Hookom
Microsoft Access MVP
:
I changed the name of Expr4 to NumberOfHead, therefore SumOfExpr4 changed to
SumOfNumberOfHead. I put in the below Equation:
=(Nz(SumOfNumberOfHead,0)+IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData,rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag,0)),0)
An error first came up saying there were too many closed parenthesis so I
added the first open one, now an error is coming up saying "The Expression
you entered contains invalid syntax, or you need to enclose your text in data
quotes"
Do you know what I put in wrong?
--
Thorson
:
If there are records but the value might be null, use:
=Nz(SumOfExpr4,
0)+IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData,rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag,0)),0)
Do yourself a favor and take the time to give appropriate names to your
expressions.
--
Duane Hookom
Microsoft Access MVP
:
"SumOfExpr4" Is a field in the query "qryMonthlyReportPG1ORR" that the Main
Report is built on. Expr4 is in a query "qryORRMonthlyDispositionRecords"
before that one, Expr4 is an equation: Expr4:
IIf((tblDispositionRecords!DispMethod="Transferred"),(qryORRMonthlyDispositionRecordsTransferred!Expr3),([Head]))
I just never changed the name of it. "SumOfExpr4" in qryMonthlyReportPG1ORR
sums up the field Expr4 in qryORRmOnthlyDispositionRecords.
Since the "SumOfExpr4" is a field I tried using the IsNull() Formula, but
I'm not quite sure how to set it up. Also as you can see from the orginal
equation the Sum of SumOfExpr4 is added to the subreport Births Total.
I would also like the equation to still add the number of births even if
"SumOfExpr4" is null.
--
Thorson
:
Where is SumOfExpr4 from? Is this a control or field or what? "Expr4"
displays a lack of effort and maintainability.
The HasData property is for reports. A report (or subreport) has this
property. A field or control can be checked for data using IsNull().
--
Duane Hookom
Microsoft Access MVP
:
Someone tried helping me out with this before, but we couldn't get the
equation he suggested to work:
I currently have an if then else statment in my report that adds the sum of
another field to the sum of animal births (if animal births has data). I
would however like this whole equation to=0 if nothing is reported, however
instead it has an #error.
I thought that edited the equation would solve the problem but I'm not sure
how to edit it. This is the orginial eqation:
=(Sum([SumOfExpr4]))+IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData,rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag,0)
This is what I tried editing it to, and it didn't work:
=IIf((SumOfExpr4.HasData),((Sum([SumOfExpr4]))+IIf(rptORRMonthlyBirthsCountSubreport.Report.HasData,rptORRMonthlyBirthsCountSubreport.Report.CountOfEarTag,0)),0)
