Calculated Totals on Main Report that includes Subreport

When there is no related child record on my subreport, my calculated total on
the main report yields #Error. However, all calculated totals work properly
as long as there is at least one child related record. How can I treat the
lack of any child related records as zero to get the calculated total to work
properly?
--
John Savage

Test the HasData property of the report in the subreport control, e.g.:

=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[Text1], 0), 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Savage" wrote in message
...
When there is no related child record on my subreport, my calculated total
on
the main report yields #Error. However, all calculated totals work
properly
as long as there is at least one child related record. How can I treat
the
lack of any child related records as zero to get the calculated total to
work
properly?
--
John Savage

John Savage wrote:

When there is no related child record on my subreport, my calculated total on
the main report yields #Error. However, all calculated totals work properly
as long as there is at least one child related record. How can I treat the
lack of any child related records as zero to get the calculated total to work
properly?


Your reference probaly looks something like this:

=subreport.Report.textbox

If so, change it to this:

=IIf(subreport.Report.HasData, subreport.Report.textbox, 0)

--
Marsh
MVP [MS Access]

I will try this. Thank you for such a quick response!

"Allen Browne" wrote:

Test the HasData property of the report in the subreport control, e.g.:

=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[Text1], 0), 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Savage" wrote in message
...
When there is no related child record on my subreport, my calculated total
on
the main report yields #Error. However, all calculated totals work
properly
as long as there is at least one child related record. How can I treat
the
lack of any child related records as zero to get the calculated total to
work
properly?
--
John Savage

Thank you Allen and Marshall for your quick response and successfull
solution. Using the HasData property was the solution to my problem. Thanks
again to both of you for your assistance!

"John Savage" wrote:

When there is no related child record on my subreport, my calculated total on
the main report yields #Error. However, all calculated totals work properly
as long as there is at least one child related record. How can I treat the
lack of any child related records as zero to get the calculated total to work
properly?
--
John Savage

Marshall,

Does this work in Access 2000? When I tried it nothing happened. This is
what I have:

=Sum([AMOUNT])-IIf(Child29.Report.HasData,Child29.Report.[Sum Of AMOUNT],0)

Am I missing something?

Also, will this string work in the orginal sum? If there is no data to sum
then I want to return 0. Is it =IIf([AMOUNT].HasData,sum([AMOUNT],0)?
Sheri

"Marshall Barton" wrote:

John Savage wrote:

When there is no related child record on my subreport, my calculated total on
the main report yields #Error. However, all calculated totals work properly
as long as there is at least one child related record. How can I treat the
lack of any child related records as zero to get the calculated total to work
properly?


Your reference probaly looks something like this:

=subreport.Report.textbox

If so, change it to this:

=IIf(subreport.Report.HasData, subreport.Report.textbox, 0)

--
Marsh
MVP [MS Access]

sheri wrote:
Does this work in Access 2000? When I tried it nothing happened. This is
what I have:

=Sum([AMOUNT])-IIf(Child29.Report.HasData,Child29.Report.[Sum Of AMOUNT],0)

I have never used A2000, but I would expect it to work if
there is nothing else wrong. Obviously, since you asked the
question, it's not working for you. If you provide more
information about what it is doing, how the subreport total
is calculated and the names of the fields and controls
involved, maybe someone can spot something that might help
you out.


Also, will this string work in the orginal sum? If there is no data to sum
then I want to return 0. Is it =IIf([AMOUNT].HasData,sum([AMOUNT],0)?

First, HasData is a report property, not a control property.

Is that in the subreport or main report? If it's in the
subreport, HasData is meaningless because a subreport with
no data will not be processed. In the main report, it would
have to be =IIf(Report.HasData,Sum([AMOUNT],0) (I think
you can get the same result from =Nz(Sum(AMOUNT),0)

--
Marsh
MVP [MS Access]

Ok so the Nz work to eliminate one issue but I have another.

What I'm attempting to accomplish is essentially a "statement" money in and
money out with a balance for the account. I have tables, queries and reports
for each. It took me FOREVER to figure out how to get as far as I am which
is Money In as the main report with a subreport for Money Out. If Money In
is Null then the Money Out doesn't show at all. Which lead me to these
posts.

The total in the subreport [MoneyOut] is =Sum([Amount])
The total in the main report [MoneyIn] is =Sum([Amount])
The Balance in the main report is now =Nz(-Child29.Report.[Sum Of
AMOUNT]+Sum([AMOUNT]),0+Sum([AMOUNT]))

Child29 is MoneyOut

The reports are linked through an account ID

sheri wrote:

Ok so the Nz work to eliminate one issue but I have another.

What I'm attempting to accomplish is essentially a "statement" money in and
money out with a balance for the account. I have tables, queries and reports
for each. It took me FOREVER to figure out how to get as far as I am which
is Money In as the main report with a subreport for Money Out. If Money In
is Null then the Money Out doesn't show at all. Which lead me to these
posts.

The total in the subreport [MoneyOut] is =Sum([Amount])
The total in the main report [MoneyIn] is =Sum([Amount])
The Balance in the main report is now =Nz(-Child29.Report.[Sum Of
AMOUNT]+Sum([AMOUNT]),0+Sum([AMOUNT]))

Child29 is MoneyOut

The reports are linked through an account ID


If there are no Money In records then the linking field
Account ID has no value and can not link to any Money Out
records.

I suspect that you might be able to do this more easily by
using just the main report and grouping in a expression
like:
=Amount = 0
to separate the Money In from the Money Out. This way the
group footer can display the subtotals by using:
=Sum(Amount)
and the report footer can display the grand total by using
the same text box expression.

--
Marsh
MVP [MS Access]

Try using the hasData property of the subreport to determine if their are any
records in the subreport.

= Sum([AMOUNT])- IIF(Child29.Report.HasData, Nz(Child29.Report.[Sum Of
AMOUNT],0),0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Marshall Barton wrote:
sheri wrote:

Ok so the Nz work to eliminate one issue but I have another.

What I'm attempting to accomplish is essentially a "statement" money in and
money out with a balance for the account. I have tables, queries and reports
for each. It took me FOREVER to figure out how to get as far as I am which
is Money In as the main report with a subreport for Money Out. If Money In
is Null then the Money Out doesn't show at all. Which lead me to these
posts.

The total in the subreport [MoneyOut] is =Sum([Amount])
The total in the main report [MoneyIn] is =Sum([Amount])
The Balance in the main report is now =Nz(-Child29.Report.[Sum Of
AMOUNT]+Sum([AMOUNT]),0+Sum([AMOUNT]))

Child29 is MoneyOut

The reports are linked through an account ID


If there are no Money In records then the linking field
Account ID has no value and can not link to any Money Out
records.

I suspect that you might be able to do this more easily by
using just the main report and grouping in a expression
like:
=Amount = 0
to separate the Money In from the Money Out. This way the
group footer can display the subtotals by using:
=Sum(Amount)
and the report footer can display the grand total by using
the same text box expression.