#Error on Calculated control - Need to test the HasData property?

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

Guest

I have a report with 6 subreports. On the Main report in a group footer, I
want to calculate a % based on totals (calculated controls) in the
subreports. I keep getting the #Error on the report. There are instances
where a subreport may have no records for the group. Do I need to test the
HasData property if there are no records? If yes, How and where do enter the
expression for the HasData property. below is the expression I am using to
calculate the %, is this correct?

=Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's
Error].[TotalErrors

txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's
Error].[ScrErrTot

txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping
Totals].[GrdTotal txt])


Appreciate any help.
Marianne
 
I think part of your problem is that you're trying to read controls from the
subreports just as if they were open as reports on their own. They're not;
they exist as subreports only as a child of the open report itself. Thus,
you refer to them via the subreport control on the report (not the subreport
name itself, but the name of the subreport control that holds the
subreport).

Second, I get around the "no data in the subreport" issue by using the
IsError function to test if the subreport's field/control has an error, and
if yes, then display an empty string or Null value.

So, taken all together, here is a generic example of how to do an IIf
function for your setup:

=IIf{IsError([SubreportName].Report.[ControlNameOnSubreport].Value), Null,
[SubreportName].Report.[ControlNameOnSubreport].Value)
 
Yes, you should use the HasData in an expression like:
=IIf(subRpt.Report.HasData,subRpt.Report.txtTotal,0)
 
Thanks so much for you help with this. I am sure this will work but I am not
putting this statement in the correct place. I tried entering it in the
control source for the calculated field int he employee footer but received
an error "The Replication ID is invalid."

Marianne

Ken Snell said:
I think part of your problem is that you're trying to read controls from the
subreports just as if they were open as reports on their own. They're not;
they exist as subreports only as a child of the open report itself. Thus,
you refer to them via the subreport control on the report (not the subreport
name itself, but the name of the subreport control that holds the
subreport).

Second, I get around the "no data in the subreport" issue by using the
IsError function to test if the subreport's field/control has an error, and
if yes, then display an empty string or Null value.

So, taken all together, here is a generic example of how to do an IIf
function for your setup:

=IIf{IsError([SubreportName].Report.[ControlNameOnSubreport].Value), Null,
[SubreportName].Report.[ControlNameOnSubreport].Value)


--

Ken Snell
<MS ACCESS MVP>

Marianne said:
I have a report with 6 subreports. On the Main report in a group footer, I
want to calculate a % based on totals (calculated controls) in the
subreports. I keep getting the #Error on the report. There are instances
where a subreport may have no records for the group. Do I need to test the
HasData property if there are no records? If yes, How and where do enter
the
expression for the HasData property. below is the expression I am using to
calculate the %, is this correct?

=Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's
Error].[TotalErrors

txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's
Error].[ScrErrTot

txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping
Totals].[GrdTotal txt])


Appreciate any help.
Marianne
Click to expand...
Click to expand...
 
I don't use Ken's method however there was a typo.
=IIf{IsError([SubreportName]....
should be
=IIf(IsError([SubreportName]....

The HasData solution sees more common usage.
--
Duane Hookom
MS Access MVP
--

Marianne said:
Thanks so much for you help with this. I am sure this will work but I am
not
putting this statement in the correct place. I tried entering it in the
control source for the calculated field int he employee footer but
received
an error "The Replication ID is invalid."

Marianne

Ken Snell said:
I think part of your problem is that you're trying to read controls from
the
subreports just as if they were open as reports on their own. They're
not;
they exist as subreports only as a child of the open report itself. Thus,
you refer to them via the subreport control on the report (not the
subreport
name itself, but the name of the subreport control that holds the
subreport).

Second, I get around the "no data in the subreport" issue by using the
IsError function to test if the subreport's field/control has an error,
and
if yes, then display an empty string or Null value.

So, taken all together, here is a generic example of how to do an IIf
function for your setup:

=IIf{IsError([SubreportName].Report.[ControlNameOnSubreport].Value),
Null,
[SubreportName].Report.[ControlNameOnSubreport].Value)


--

Ken Snell
<MS ACCESS MVP>

Marianne said:
I have a report with 6 subreports. On the Main report in a group footer,
I
want to calculate a % based on totals (calculated controls) in the
subreports. I keep getting the #Error on the report. There are
instances
where a subreport may have no records for the group. Do I need to test
the
HasData property if there are no records? If yes, How and where do
enter
the
expression for the HasData property. below is the expression I am using
to
calculate the %, is this correct?

=Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's
Error].[TotalErrors

txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's
Error].[ScrErrTot

txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping
Totals].[GrdTotal txt])


Appreciate any help.
Marianne
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the catch, Duane.

--

Ken Snell
<MS ACCESS MVP>

Duane Hookom said:
I don't use Ken's method however there was a typo.
=IIf{IsError([SubreportName]....
should be
=IIf(IsError([SubreportName]....

The HasData solution sees more common usage.
--
Duane Hookom
MS Access MVP
--

Marianne said:
Thanks so much for you help with this. I am sure this will work but I am
not
putting this statement in the correct place. I tried entering it in the
control source for the calculated field int he employee footer but
received
an error "The Replication ID is invalid."

Marianne

Ken Snell said:
I think part of your problem is that you're trying to read controls from
the
subreports just as if they were open as reports on their own. They're
not;
they exist as subreports only as a child of the open report itself.
Thus,
you refer to them via the subreport control on the report (not the
subreport
name itself, but the name of the subreport control that holds the
subreport).

Second, I get around the "no data in the subreport" issue by using the
IsError function to test if the subreport's field/control has an error,
and
if yes, then display an empty string or Null value.

So, taken all together, here is a generic example of how to do an IIf
function for your setup:

=IIf{IsError([SubreportName].Report.[ControlNameOnSubreport].Value),
Null,
[SubreportName].Report.[ControlNameOnSubreport].Value)


--

Ken Snell
<MS ACCESS MVP>

I have a report with 6 subreports. On the Main report in a group
footer, I
want to calculate a % based on totals (calculated controls) in the
subreports. I keep getting the #Error on the report. There are
instances
where a subreport may have no records for the group. Do I need to test
the
HasData property if there are no records? If yes, How and where do
enter
the
expression for the HasData property. below is the expression I am
using to
calculate the %, is this correct?

=Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's
Error].[TotalErrors

txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's
Error].[ScrErrTot

txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping
Totals].[GrdTotal txt])


Appreciate any help.
Marianne
Click to expand...
Click to expand...
Click to expand...
 
Back
Top