Need help with sum on a report

[Slez via AccessMonster.com]

I'm trying to display the sum of data in a report footer, but can't quite get
the expression to work.

In one of the grouping levels, BidNumber, the header contains a control named
BidType, and another calculated control that sums records in the detail
section of the report. The name of that control is txtBidTotal. I only want
the sum of the values where [BidType] = "Base Bid".

The expression I created is:
=Sum(IIf([BidType]="Base Bid",[txtBidTotal],0))

When I run the report, it prompts me for txtBidTotal. I can click OK and it
returns with a value of "0".
Any suggestions?
Thanks
Slez

 

[Marshall Barton]

I'm trying to display the sum of data in a report footer, but can't quite get
the expression to work.

In one of the grouping levels, BidNumber, the header contains a control named
BidType, and another calculated control that sums records in the detail
section of the report. The name of that control is txtBidTotal. I only want
the sum of the values where [BidType] = "Base Bid".

The expression I created is:
=Sum(IIf([BidType]="Base Bid",[txtBidTotal],0))

The aggregate function only operate on **fields** in the
form/report's record source table/query. They are unaware
of **controls** on the form/report.

The solution usually is to simply use the same expression
that you ued in the group header/footer. You never did say
what expression you have in txtBidTotal, but if it's
something like =Sum(Price & Quantity) then the report
footer text box would use the expression:

=Sum(IIf([BidType]="Base Bid",Price & Quantity,0))

 

[Slez via AccessMonster.com]

Thanks Marshall! I see how that works now. I'm trying to take this one step
further so I have another question.

I'm trying to sum fields on the report from 2 different levels: One from the
detail section + 2 other fields from the group header/footer. The
combination of the 2 is causing me some frustration.

Incidentally, the expression in txtBidTotal is:
=Sum([LineTotalSellPrice])+[SalesTaxAmount]+[UseTaxAmount]
LineTotalSellPrice exists in the detail section so there are multiple records.

SalesTaxAmount & UseTaxAmount exist only once per group.

I achieved an accurate total of the LineTotalSellPrice where BidType = "Base
Bid" with:
=Sum(IIf([BidType]="Base Bid",[LineTotalSellPrice],0))

The problem I come up with is that I can't get it to add just one
SalesTaxAmount & UseTaxAmount per group. It adds one per each record in the
detail section. I can get the IIf statement to work properly in the group
header/footer, but that leaves me with not being able to sum those fields.
It would be really nice if aggregate fuctions recognized controls! My
thought was to create 3 text boxes in the report footer: 1 to total
LineTotalSellPrice, 1 to total SalesTaxAmount, and 1 to total UseTaxAmount,
and then I could total those 3 controls.

I have tried numerous different configurations of expressions, but it comes
down to getting it to add just one SalesTaxAmount & UseTaxAmount per group
where BidType = "Base Bid". If you can offer any further suggestions, I
would greatly appreciate it!
Thanks again!
Slez

I'm trying to display the sum of data in a report footer, but can't quite get
the expression to work.

[quoted text clipped - 6 lines]

The expression I created is:
=Sum(IIf([BidType]="Base Bid",[txtBidTotal],0))

The aggregate function only operate on **fields** in the
form/report's record source table/query. They are unaware
of **controls** on the form/report.

The solution usually is to simply use the same expression
that you ued in the group header/footer. You never did say
what expression you have in txtBidTotal, but if it's
something like =Sum(Price & Quantity) then the report
footer text box would use the expression:

=Sum(IIf([BidType]="Base Bid",Price & Quantity,0))

 

[Slez via AccessMonster.com]

Whoa! Hold the phone! It wasn't even 2 minutes after I sent this to post
that I figured out I could create a control named txtBaseBidTax with the
expression:
=(IIf([BidType]="Base Bid",[UseTaxAmount]+[SalesTaxAmount],0))
and set the Running Sum property to "Over All"...This was the key!

I then created a control in the report footer with the Control Source set to:
=[txtBaseBidTax].

I can now total the 2 controls in the report footer and get the accurate
total I was looking for! Success is sweet!...even though it's a measly (sp?)
little expression!
Slez

Thanks Marshall! I see how that works now. I'm trying to take this one step
further so I have another question.

I'm trying to sum fields on the report from 2 different levels: One from the
detail section + 2 other fields from the group header/footer. The
combination of the 2 is causing me some frustration.

Incidentally, the expression in txtBidTotal is:
=Sum([LineTotalSellPrice])+[SalesTaxAmount]+[UseTaxAmount]
LineTotalSellPrice exists in the detail section so there are multiple records.

SalesTaxAmount & UseTaxAmount exist only once per group.

I achieved an accurate total of the LineTotalSellPrice where BidType = "Base
Bid" with:
=Sum(IIf([BidType]="Base Bid",[LineTotalSellPrice],0))

The problem I come up with is that I can't get it to add just one
SalesTaxAmount & UseTaxAmount per group. It adds one per each record in the
detail section. I can get the IIf statement to work properly in the group
header/footer, but that leaves me with not being able to sum those fields.
It would be really nice if aggregate fuctions recognized controls! My
thought was to create 3 text boxes in the report footer: 1 to total
LineTotalSellPrice, 1 to total SalesTaxAmount, and 1 to total UseTaxAmount,
and then I could total those 3 controls.

I have tried numerous different configurations of expressions, but it comes
down to getting it to add just one SalesTaxAmount & UseTaxAmount per group
where BidType = "Base Bid". If you can offer any further suggestions, I
would greatly appreciate it!
Thanks again!
Slez
[quoted text clipped - 13 lines]

=Sum(IIf([BidType]="Base Bid",Price & Quantity,0))

 

[Marshall Barton]

Whoa! Hold the phone! It wasn't even 2 minutes after I sent this to post
that I figured out I could create a control named txtBaseBidTax with the
expression:
=(IIf([BidType]="Base Bid",[UseTaxAmount]+[SalesTaxAmount],0))
and set the Running Sum property to "Over All"...This was the key!

I then created a control in the report footer with the Control Source set to:
=[txtBaseBidTax].

I can now total the 2 controls in the report footer and get the accurate
total I was looking for! Success is sweet!...even though it's a measly (sp?)
little expression!

Way to go! That is exactly the right way to total
calculated controls in group a header/footer section.

RunnungSum text boxes are a powerful tool that can be used
in many different situations. E.g. a detail text box with
expression =1 can be used as a line number on the details.
The line number can in turn be used in the Format event
procedure to process the first or last detail differently
from the other details.