Calculated Totals on Main Report that includes Subreport

John Savage · External Since: Oct 20, 2004 Posts: 3

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 Barton · External Since: Dec 07, 2003 Posts: 5120

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]

Allen Browne · External Since: Nov 08, 2003 Posts: 7201

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 - <a style='text-decoration: underline;' href="http://allenbrowne.com/tips.html" target="_blank">http://allenbrowne.com/tips.html</a>
Reply to group, rather than allenbrowne at mvps dot org.

"John Savage" <JohnSavage.RemoveThis@discussions.microsoft.com> wrote in message
news:12BA6A76-6A64-4C1F-9E2B-826993F87305@microsoft.com...
> 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 · External Since: Oct 20, 2004 Posts: 3

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.
<font color=purple> > Tips for Access users - <a style='text-decoration: underline;' href="http://allenbrowne.com/tips.html</font" target="_blank">http://allenbrowne.com/tips.html</font</a>>
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "John Savage" <JohnSavage.DeleteThis@discussions.microsoft.com> wrote in message
> news:12BA6A76-6A64-4C1F-9E2B-826993F87305@microsoft.com...
  > > 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 · External Since: Oct 20, 2004 Posts: 3

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

sheri · External Since: Nov 06, 2006 Posts: 8

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]
>

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

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]

sheri · External Since: Nov 06, 2006 Posts: 8

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

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

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]

John Spencer · External Since: Jul 15, 2009 Posts: 47

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.
>

sheri · External Since: Nov 06, 2006 Posts: 8

Thanks for the response but my data is from two tables so unfortunately
simplicity doesn't work at least not that I've found. I've managed to get
some of what I need but have other issues Please see my other post named
Running Sum changes when not visible in the report section.

Thanks again.

"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.
>
> --
> Marsh
> MVP [MS Access]
> .
>

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

How does having two tables make it complex?

Using a query that joins two tables as a report's record
source is a very common practice. Just create a new query
and add both tables. Drag the linking field from one table
to the linking field in the other table to join the tables.
Then drag the needed fields from both tables to the query's
field list.

Use Sorting and Grouping (View menu) in the report to get
group header and footer sections for the one side table
fields and put the many side fields in the detail section.

I really think that my earlier suggestion about doing away
with the subreport and adding another group on the simple
expression =Amount >= 0 will separate the (detail) In and
Out records so it all looks like you have a subreport, but
without the complications you are struggling with.
--
Marsh
MVP [MS Access]

sheri wrote:
>Thanks for the response but my data is from two tables so unfortunately
>simplicity doesn't work at least not that I've found. I've managed to get
>some of what I need but have other issues Please see my other post named
>Running Sum changes when not visible in the report section.
>
>"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.

sheri · External Since: Nov 06, 2006 Posts: 8

I'm certainly not saying two tables make it complex just not simple. There
is not a one to many relationship between the two tables and that makes it
more complex, at least to me. There is a many to many relationship in that
there are many money's in and many money's out related by an ID. So for each
ID there is a one to many relationship to money's in and money's out. But
I've attempted using a single report and it wants to return repeating data
matching each money's in with each money's out. I've gotten it almost there
using subreports but the in the post I mentioned before the running sum gives
me a different number when not visible verses when visible (correct sum).
Have you run into this issue?

It may be that I don't know what you are suggesting because it really does
seem like there should be a very simple answer.

Without a subreport this is what happens:

So in more detail - We get donations. Some for general use (unrestricted)
and some are designated with specific use (temporarily restricted). Some
donations are specifically designated for a student (temporarily restricted
with a specific Student ID).

The donation table relates to the student table via the student ID. If a
student is not associated with a donation (i.e. general use funds) then the
student ID is left blank. I could set a null value but that really doesn't
seem to be the issue here. I do not relate donations to the expense table in
the donation table purposely because more than one check could be associated
with a deposit. This is done in the expense table because typically one
check will not relate to more than one donation.

In the normal course of business we write checks to cover expenses. Some
checks related back to specific donations (designated by the donation ID),
while other are for general program expenses (no donation ID). Some checks
relate specifically to students (designated by the Student ID). These may or
may not relate to a specific donation.

Example1: We receive a donation of $1000 for a party. This is temporarily
restricted due to the designation for a party but it doesn't relate to a
student. We will have related checks when we pay for decor or food, etc
which will will designate with the donationID.

Example2: We receive a donation of $500 for a student scholarship to be
used for college expenses or living expense - Tracy Smith. We will designate
this as restricted and identify it with the student ID. Tracy is going to
school and needs money for books. We write a check for $200 to the book
store and designate it with the student ID. Since it is very likely that
Tracy received various scholarships we probably will not related the check to
the donationID but we may. Lets say we then write her another check for $200
for living expenses. What I'm wanting to see in a report is:

Tracy Smith Student ID 20

1/1/09 $500 Scholarship for college and living expenses
6/30/09 $200 1100 Book Store Books for college
7/15/09 $200 1101 Tracy Smith Living expenses

Balance $100

What it is giving me is this:

Tracy Smith Student ID 20

1/1/09 $500 Scholarship for college and living expenses
6/30/09 $200 1100 Book Store Books for college
1/1/09 $500 Scholarship for college and living expenses
7/15/09 $200 1101 Tracy Smith Living expenses

Balance $600

And if I had another donation for Tracy it would list the other donation
with all the checks again.

This is the best I can do to explain what is happening.

Sheri

"Marshall Barton" wrote:

> How does having two tables make it complex?
>
> Using a query that joins two tables as a report's record
> source is a very common practice. Just create a new query
> and add both tables. Drag the linking field from one table
> to the linking field in the other table to join the tables.
> Then drag the needed fields from both tables to the query's
> field list.
>
> Use Sorting and Grouping (View menu) in the report to get
> group header and footer sections for the one side table
> fields and put the many side fields in the detail section.
>
> I really think that my earlier suggestion about doing away
> with the subreport and adding another group on the simple
> expression =Amount >= 0 will separate the (detail) In and
> Out records so it all looks like you have a subreport, but
> without the complications you are struggling with.
> --
> Marsh
> MVP [MS Access]
>
>
> sheri wrote:
> >Thanks for the response but my data is from two tables so unfortunately
> >simplicity doesn't work at least not that I've found. I've managed to get
> >some of what I need but have other issues Please see my other post named
> >Running Sum changes when not visible in the report section.
> >
> >"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.
>
> .
>

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

sheri wrote:

>I'm certainly not saying two tables make it complex just not simple. There
>is not a one to many relationship between the two tables and that makes it
>more complex, at least to me. There is a many to many relationship in that
>there are many money's in and many money's out related by an ID. So for each
>ID there is a one to many relationship to money's in and money's out. But
>I've attempted using a single report and it wants to return repeating data
>matching each money's in with each money's out. I've gotten it almost there
>using subreports but the in the post I mentioned before the running sum gives
>me a different number when not visible verses when visible (correct sum).
>Have you run into this issue?
>
>It may be that I don't know what you are suggesting because it really does
>seem like there should be a very simple answer.
>
>Without a subreport this is what happens:
>
>So in more detail - We get donations. Some for general use (unrestricted)
>and some are designated with specific use (temporarily restricted). Some
>donations are specifically designated for a student (temporarily restricted
>with a specific Student ID).
>
>The donation table relates to the student table via the student ID. If a
>student is not associated with a donation (i.e. general use funds) then the
>student ID is left blank. I could set a null value but that really doesn't
>seem to be the issue here. I do not relate donations to the expense table in
>the donation table purposely because more than one check could be associated
>with a deposit. This is done in the expense table because typically one
>check will not relate to more than one donation.
>
>In the normal course of business we write checks to cover expenses. Some
>checks related back to specific donations (designated by the donation ID),
>while other are for general program expenses (no donation ID). Some checks
>relate specifically to students (designated by the Student ID). These may or
>may not relate to a specific donation.
>
>Example1: We receive a donation of $1000 for a party. This is temporarily
>restricted due to the designation for a party but it doesn't relate to a
>student. We will have related checks when we pay for decor or food, etc
>which will will designate with the donationID.
>
>Example2: We receive a donation of $500 for a student scholarship to be
>used for college expenses or living expense - Tracy Smith. We will designate
>this as restricted and identify it with the student ID. Tracy is going to
>school and needs money for books. We write a check for $200 to the book
>store and designate it with the student ID. Since it is very likely that
>Tracy received various scholarships we probably will not related the check to
>the donationID but we may. Lets say we then write her another check for $200
>for living expenses. What I'm wanting to see in a report is:
>
>Tracy Smith Student ID 20
>
>1/1/09 $500 Scholarship for college and living expenses
>6/30/09 $200 1100 Book Store Books for college
>7/15/09 $200 1101 Tracy Smith Living expenses
>
>Balance $100
>
>What it is giving me is this:
>
>Tracy Smith Student ID 20
>
>1/1/09 $500 Scholarship for college and living expenses
>6/30/09 $200 1100 Book Store Books for college
>1/1/09 $500 Scholarship for college and living expenses
>7/15/09 $200 1101 Tracy Smith Living expenses
>
>Balance $600
>
>And if I had another donation for Tracy it would list the other donation
>with all the checks again.

You may have many In records and many Out records, but they
are not in a relationship with each other. Trying to treat
them as if there were a relationship will create the mess
you are now seeing.

You have two one to many relationships from the students
table. I had thought that the In and Out records were in
the same table with a field that indicated if a record was
an In record or an Out record. With this arrangement, you
would not be seeing the duplication.

If your In and Out tables both have the same fields, they
should be combined in a single table and your problem would
disappear.

If the tables have a real difference in the data they
contain, then, for the purposes of this report you should
create a query that acts as a virtual table that combines
the data from the In and Out tables. The general idea would
be like:

Query Combined:
SELECT ID, datefield, "IN" As InOut,
Amount, descriptionfield
FROM InTable
UNION ALL
SELECT ID, datefield, "Out", Amount, descriptiionfield
FROM OutTable

Then your report's record source query would simply be like:

SELECT Students.ID, Students.studentnamefield,
Combined.InOut, Combined.Amount,
Combined.descriptiionfield
FROM Students INNER JOIN Combined
ON Students.ID = Combined.ID

With this kind of arrangement, I'm pretty sure the report
structure I posted earlier will do what you want.

--
Marsh
MVP [MS Access]

sheri · External Since: Nov 06, 2006 Posts: 8

This is why I'm using subreports. And it's working except for the running
sum visibility issue. Any clue on the running sum issue?

I can try what you are suggesting but I'd rather fix the above issue because
everything else is working.

"Marshall Barton" wrote:

> You may have many In records and many Out records, but they
> are not in a relationship with each other. Trying to treat
> them as if there were a relationship will create the mess
> you are now seeing.
>
> You have two one to many relationships from the students
> table. I had thought that the In and Out records were in
> the same table with a field that indicated if a record was
> an In record or an Out record. With this arrangement, you
> would not be seeing the duplication.
>
> If your In and Out tables both have the same fields, they
> should be combined in a single table and your problem would
> disappear.
>
> If the tables have a real difference in the data they
> contain, then, for the purposes of this report you should
> create a query that acts as a virtual table that combines
> the data from the In and Out tables. The general idea would
> be like:
>
> Query Combined:
> SELECT ID, datefield, "IN" As InOut,
> Amount, descriptionfield
> FROM InTable
> UNION ALL
> SELECT ID, datefield, "Out", Amount, descriptiionfield
> FROM OutTable
>
> Then your report's record source query would simply be like:
>
> SELECT Students.ID, Students.studentnamefield,
> Combined.InOut, Combined.Amount,
> Combined.descriptiionfield
> FROM Students INNER JOIN Combined
> ON Students.ID = Combined.ID
>
> With this kind of arrangement, I'm pretty sure the report
> structure I posted earlier will do what you want.
>
> --
> Marsh
> MVP [MS Access]
> .
>

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

sheri wrote:

>This is why I'm using subreports. And it's working except for the running
>sum visibility issue. Any clue on the running sum issue?
>
>I can try what you are suggesting but I'd rather fix the above issue because
>everything else is working.

But, before, you said you were using one subreport for the
Out records and the main report was based on the In table.
Because those tables are NOT related, it can't work.

To use subreport's, the main report would be based on the
Students table with no In or Out data. Then you could use
two subreports in the main report's detail section, one for
the In table and the other for the Out table. Both
subreport controls' LinkMaster/Child properties would be the
StudentID fields. Then a total text box, also in the main
report's detail seection would be like:
=IIf(Insubreport.Report.HasData, Insubreport.Report.InTotal,
0) - IIf(Outsubreport.Report.HasData,
Outsubreport.Report.InTotal, 0)

--
Marsh
MVP [MS Access]

sheri · External Since: Nov 06, 2006 Posts: 8

Right, things changed since my original post and I figured these things out.
Now I have a running sum issue.

I've identified where the error is occuring but I still don't understand why.
The running sum is in the grouping footer next to the subreport (also in the
grouping footer). So for one of my records it is picking up half the amount.
It shows the total for the record to be $1000 but only adds $500 to the
running sum. The record is the sum of two $500 amounts. It is not the only
record with two amounts so it's not happening every time. This is baffling.

I'm using a subreport that also has a subreport. Everything works correctly
when visible just not when invisible. Just so weird.

I appreciate your input.

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

sheri wrote:

>Right, things changed since my original post and I figured these things out.
>Now I have a running sum issue.
>
>I've identified where the error is occuring but I still don't understand why.
>The running sum is in the grouping footer next to the subreport (also in the
>grouping footer). So for one of my records it is picking up half the amount.
> It shows the total for the record to be $1000 but only adds $500 to the
>running sum. The record is the sum of two $500 amounts. It is not the only
>record with two amounts so it's not happening every time. This is baffling.
>
>I'm using a subreport that also has a subreport. Everything works correctly
>when visible just not when invisible. Just so weird.

And what is the arrangement for this running sum?

I have no idea what you are doing with a subsubreport. Is
it relevant to this new problem?

Where is the other subreport? I expect both the In and Our
subreports to be in the same section!

--
Marsh
MVP [MS Access]

sheri · External Since: Nov 06, 2006 Posts: 8

I think I got it while writing out the details for you in a new post (which I
won't post). I still don't understand why the running sum would change based
on visibility, but I think I can get what I need now anyway. If not, I'll be
back!

Thanks for sticking it out with me!

"Marshall Barton" wrote:

> And what is the arrangement for this running sum?
>
> I have no idea what you are doing with a subsubreport. Is
> it relevant to this new problem?
>
> Where is the other subreport? I expect both the In and Our
> subreports to be in the same section!
>
> --
> Marsh
> MVP [MS Access]
> .
>