Need help with an Iif expression

[Slez via AccessMonster.com]

I need a little help with how to structure an expression. I'm trying to
establish some totals on a report with some specific contitions. My fields
are as follows:

[BidType] has 3 possible values - Alternate, Base Bid, Budget
[BidStatus] has 4 possible values - Awarded, Not Awarded, Pending, Void

I need the sum of records of a control called txtBidTotal where [BidType]
equals "Alternate" or "Base Bid" AND [BidStatus] equals "Awarded".

I have created Iif statements with one condition, but would appreciate some
help with how to structure this multi-condition one. Thanks in advance for
any help!
Slez

 

[Guest]

You can't sum controls. You can sum expressions created from fields in your
report's record source. Summing with conditions might look like:
=Sum(Abs( ([BidType]="Alternate" or [BidType]="Base Bid") AND
[BidStatus]="Awarded") * ...your expression...)

 

[Slez via AccessMonster.com]

Thanks for the reply! I sort of mispoke about summing a control. Let me
explain how my report is currently structured in a little more detail.

BidNumber is a group level with a header/footer. [txtBidTotal] is the name
of a control that adds the currency value of records in the detail section
plus 2 other currency fields in the BidNumber header. In the BidNumber
footer, is a control named txtBaseBidTotal with the control source:
=(Iif([BidType]="Base Bid",[txtBidTotal],0))
The Running Sum property is set to Over Group.

Then I have a quarterly group level footer control which has the Control
Source set to:
=[txtBaseBidTotal]
This gives the total for that group level.

All of that works great, so I'd like to add a control in the BidNumber footer
with the conditions previously mentioned. Based on your response, I should
have something similar to:
=(Iif((([BidType]="Base Bid" Or [BidType]="Alternate") AND ([BidStatus]
="Awarded")) ,[txtBidTotal],0))
...and set the Running Sum property to Over Group.

I'm going to give that a try, but am curious if you agree?
Thanks again!
Slez

You can't sum controls. You can sum expressions created from fields in your
report's record source. Summing with conditions might look like:
=Sum(Abs( ([BidType]="Alternate" or [BidType]="Base Bid") AND
[BidStatus]="Awarded") * ...your expression...)

I need a little help with how to structure an expression. I'm trying to
establish some totals on a report with some specific contitions. My fields

[quoted text clipped - 10 lines]

any help!
Slez

 

[Guest]

It should work but I am not certain. I rarely if ever use running sum for
this type of calculation. I prefer to create totals queries with the exact
values I need and then add them to the report's record source.

--
Duane Hookom
Microsoft Access MVP

Thanks for the reply! I sort of mispoke about summing a control. Let me
explain how my report is currently structured in a little more detail.

BidNumber is a group level with a header/footer. [txtBidTotal] is the name
of a control that adds the currency value of records in the detail section
plus 2 other currency fields in the BidNumber header. In the BidNumber
footer, is a control named txtBaseBidTotal with the control source:
=(Iif([BidType]="Base Bid",[txtBidTotal],0))
The Running Sum property is set to Over Group.

Then I have a quarterly group level footer control which has the Control
Source set to:
=[txtBaseBidTotal]
This gives the total for that group level.

All of that works great, so I'd like to add a control in the BidNumber footer
with the conditions previously mentioned. Based on your response, I should
have something similar to:
=(Iif((([BidType]="Base Bid" Or [BidType]="Alternate") AND ([BidStatus]
="Awarded")) ,[txtBidTotal],0))
...and set the Running Sum property to Over Group.

I'm going to give that a try, but am curious if you agree?
Thanks again!
Slez

You can't sum controls. You can sum expressions created from fields in your
report's record source. Summing with conditions might look like:
=Sum(Abs( ([BidType]="Alternate" or [BidType]="Base Bid") AND
[BidStatus]="Awarded") * ...your expression...)

I need a little help with how to structure an expression. I'm trying to
establish some totals on a report with some specific contitions. My fields

[quoted text clipped - 10 lines]

any help!
Slez