Calculation on Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have a main report and several subreports. I am calculating a field
from each one of these subreports into an unbound text box on the main report.

=[rptPMRentRollTab1BaseRent
subreport].[Report].[rentamount]+[rptPMRentRollTab2BaseCAM
subreport].[Report].[camamount]+[rptPMRentRollTab4BasePtax
subreport].[Report].[PTAXamount]+[rptPMRentRollTab6BaseINS
subreport].[Report].[INSamount]+[rptPMRentRollOpCostTab
subreport].[Report].[Amount]

It works on those files where there is a dollar amount in the field. But
when the field is $0.00, then this returns "#Error". Any suggestions?
 
I suspect that the field value isn't really $0.00. You have probably
formatted a Null value to display this way. If that is the case, use Nz() in
your equation to change the Null values to 0.

=Nz([rptPMRentRollTab1BaseRent
subreport].[Report].[rentamount],0)+Nz([rptPMRentRollTab2BaseCAM
subreport].[Report].[camamount],0)+Nz([rptPMRentRollTab4BasePtax
subreport].[Report].[PTAXamount],0)+Nz([rptPMRentRollTab6BaseINS
subreport].[Report].[INSamount],0)+Nz([rptPMRentRollOpCostTab
subreport].[Report].[Amount],0)
 
Lori said:
Hello, I have a main report and several subreports. I am calculating a field
from each one of these subreports into an unbound text box on the main report.

=[rptPMRentRollTab1BaseRent
subreport].[Report].[rentamount]+[rptPMRentRollTab2BaseCAM
subreport].[Report].[camamount]+[rptPMRentRollTab4BasePtax
subreport].[Report].[PTAXamount]+[rptPMRentRollTab6BaseINS
subreport].[Report].[INSamount]+[rptPMRentRollOpCostTab
subreport].[Report].[Amount]

It works on those files where there is a dollar amount in the field. But
when the field is $0.00, then this returns "#Error". Any suggestions?
Click to expand...


You will not get #Error if the value in the subreport is
zero. You will get the error if the subreport's value is a
text string. You will also get the error if the subreport
has no records to process.

In the former case use

=CCur([rptPMRentRollTab1BaseRent
subreport].[Report].[rentamount]+ . . .

If a subreport might not have any records, then use:

=IIf([rptPMRentRollTab1BaseRent
subreport].[Report].HasData, [rptPMRentRollTab1BaseRent
subreport].[Report].[rentamount], 0)+ . . .
 
Thanks, I had a Y/N data type that in the query I had the criteria set to
true, so I had to make sure that in the records where there was no records
that the y/n field was true. Anyway...thanks for your suggestions.

I have another question for you, the calculated field pulls from a
continuous subform. And like I said, it would be those I have set to "Yes"
or (true). In a couple of these records, more than one is set to Yes with
different $ amts in each record. While the data is pulling over on the
report. The calculation only calculates the first one marked yes in that
subreport. How would I have it pick up all the figures? If you need more
info, let me know...I hope I described it correctly.

--
Thank, Lori


Marshall Barton said:
Lori said:
Hello, I have a main report and several subreports. I am calculating a field
from each one of these subreports into an unbound text box on the main report.

=[rptPMRentRollTab1BaseRent
subreport].[Report].[rentamount]+[rptPMRentRollTab2BaseCAM
subreport].[Report].[camamount]+[rptPMRentRollTab4BasePtax
subreport].[Report].[PTAXamount]+[rptPMRentRollTab6BaseINS
subreport].[Report].[INSamount]+[rptPMRentRollOpCostTab
subreport].[Report].[Amount]

It works on those files where there is a dollar amount in the field. But
when the field is $0.00, then this returns "#Error". Any suggestions?
Click to expand...


You will not get #Error if the value in the subreport is
zero. You will get the error if the subreport's value is a
text string. You will also get the error if the subreport
has no records to process.

In the former case use

=CCur([rptPMRentRollTab1BaseRent
subreport].[Report].[rentamount]+ . . .

If a subreport might not have any records, then use:

=IIf([rptPMRentRollTab1BaseRent
subreport].[Report].HasData, [rptPMRentRollTab1BaseRent
subreport].[Report].[rentamount], 0)+ . . .
Click to expand...
 
Lori said:
Thanks, I had a Y/N data type that in the query I had the criteria set to
true, so I had to make sure that in the records where there was no records
that the y/n field was true. Anyway...thanks for your suggestions.

I have another question for you, the calculated field pulls from a
continuous subform. And like I said, it would be those I have set to "Yes"
or (true). In a couple of these records, more than one is set to Yes with
different $ amts in each record. While the data is pulling over on the
report. The calculation only calculates the first one marked yes in that
subreport. How would I have it pick up all the figures? If you need more
info, let me know...I hope I described it correctly.
Click to expand...


Subform?? How does a continuous subform come into play??

If you were talking about a (sub)report, I would suggest
using the Sum function in the report's footer section.
 
Back
Top