Final totals include deleted info

[Bob Howard]

Hi,

I've successfully configured my report so that it selectively (based on data
content) deletes various sections by performing a Cancel = True in the
OnFormat event for each section, as needed. In some cases, the detail
section is deleted as well ... this is my intended result.

However, the amounts from the deleted detail section(s) are being included
in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).

I was "hoping" that this would not occur ... I only want the final totals to
include what was included in the report.

Any hints how to get around this??

Thanks...

bob

 

[KARL DEWEY]

However, the amounts from the deleted detail section(s) are being included
in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).
Include an IIF function in the sum like this.
=Sum(IIF([Cancel = False, [....],0))

 

[Marshall Barton]

I've successfully configured my report so that it selectively (based on data
content) deletes various sections by performing a Cancel = True in the
OnFormat event for each section, as needed. In some cases, the detail
section is deleted as well ... this is my intended result.

However, the amounts from the deleted detail section(s) are being included
in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).

I was "hoping" that this would not occur ... I only want the final totals to
include what was included in the report.

If you do not want data included in a report, then the
report's record source query should filter the data to
exclude those records. This is especially true if you do
not want that data included in totals.

If you have a scenario where the records really are required
for some strange reason, then you could use a RunningSum
text box on a conditional expression to add 0 for the hidden
records.

 

[Bob Howard]

I need all the records in order to make the decisions on whether to hide the
various report sections ... and the calculations are really way too
complicated for a query and seem best suited to be done in the report (some
of the decisions are based on the content of a single record, and some of
the decisions are based on the sum of a set of records).

I'll try the other suggestion and see what happens.

Thanks...

bob

I've successfully configured my report so that it selectively (based on
data
content) deletes various sections by performing a Cancel = True in the
OnFormat event for each section, as needed. In some cases, the detail
section is deleted as well ... this is my intended result.

However, the amounts from the deleted detail section(s) are being included
in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).

I was "hoping" that this would not occur ... I only want the final totals
to
include what was included in the report.

If you do not want data included in a report, then the
report's record source query should filter the data to
exclude those records. This is especially true if you do
not want that data included in totals.

If you have a scenario where the records really are required
for some strange reason, then you could use a RunningSum
text box on a conditional expression to add 0 for the hidden
records.

 

[Bob Howard]

I'll give it a whirl and let you know... Thanks, bob

in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).
Include an IIF function in the sum like this.
=Sum(IIF([Cancel = False, [....],0))

--
KARL DEWEY
Build a little - Test a little

Hi,

I've successfully configured my report so that it selectively (based on
data
content) deletes various sections by performing a Cancel = True in the
OnFormat event for each section, as needed. In some cases, the detail
section is deleted as well ... this is my intended result.

However, the amounts from the deleted detail section(s) are being
included
in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).

I was "hoping" that this would not occur ... I only want the final totals
to
include what was included in the report.

Any hints how to get around this??

Thanks...

bob

 

[Bob Howard]

Actually, on thinking about it I'm not sure how to implement your
suggestion. The =Sum function is specified right in the ControlSource
property for the sum control. Can you be more specific? ... bob

in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).
Include an IIF function in the sum like this.
=Sum(IIF([Cancel = False, [....],0))

--
KARL DEWEY
Build a little - Test a little

Hi,

I've successfully configured my report so that it selectively (based on
data
content) deletes various sections by performing a Cancel = True in the
OnFormat event for each section, as needed. In some cases, the detail
section is deleted as well ... this is my intended result.

However, the amounts from the deleted detail section(s) are being
included
in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).

I was "hoping" that this would not occur ... I only want the final totals
to
include what was included in the report.

Any hints how to get around this??

Thanks...

bob

 

[KARL DEWEY]

I just modified what you posted. Apply the IIF function to the sum statement.
--
KARL DEWEY
Build a little - Test a little

Actually, on thinking about it I'm not sure how to implement your
suggestion. The =Sum function is specified right in the ControlSource
property for the sum control. Can you be more specific? ... bob

However, the amounts from the deleted detail section(s) are being
included

in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).
Include an IIF function in the sum like this.
=Sum(IIF([Cancel = False, [....],0))

--
KARL DEWEY
Build a little - Test a little

Hi,

I've successfully configured my report so that it selectively (based on
data
content) deletes various sections by performing a Cancel = True in the
OnFormat event for each section, as needed. In some cases, the detail
section is deleted as well ... this is my intended result.

However, the amounts from the deleted detail section(s) are being
included
in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).

I was "hoping" that this would not occur ... I only want the final totals
to
include what was included in the report.

Any hints how to get around this??

Thanks...

bob

 

[Marshall Barton]

Then try Karl's conditional sum if you can easily identify
the records to include or exclude:
=Sum(IIf(somefield = somevalue, otherfield, 0))
just remember that the aggregate function's only operate on
fields in the report's record source table/query. Since you
said that the report has to do some complex calculations to
determine the condition, this approach probably won't work
in your case.

Another way is to have your calculations save a true/false
value in a hidden text box in the group header section or in
the detail section. Then you can use a textbox in the
detail section with its RunningSum property. The running
sum text box's expression could then be like:
=IIf(truefalsetextbox, otherfield, 0)
--
Marsh
MVP [MS Access]

I need all the records in order to make the decisions on whether to hide the
various report sections ... and the calculations are really way too
complicated for a query and seem best suited to be done in the report (some
of the decisions are based on the content of a single record, and some of
the decisions are based on the sum of a set of records).

I'll try the other suggestion and see what happens.

I've successfully configured my report so that it selectively (based on
data
content) deletes various sections by performing a Cancel = True in the
OnFormat event for each section, as needed. In some cases, the detail
section is deleted as well ... this is my intended result.

However, the amounts from the deleted detail section(s) are being included
in the report's final totals (I use =Sum([....]) on the field name to
compute the final total).

I was "hoping" that this would not occur ... I only want the final totals
to
include what was included in the report.

If you do not want data included in a report, then the
report's record source query should filter the data to
exclude those records. This is especially true if you do
not want that data included in totals.

If you have a scenario where the records really are required
for some strange reason, then you could use a RunningSum
text box on a conditional expression to add 0 for the hidden
records.