Totals with Condition

  • Thread starter Thread starter Brennan
  • Start date Start date
B

Brennan

Hello:

I have a report that has a text box used to sum some
information in a text box. The following code is present
in the text box:

=Sum(IIf([tblBids_Status]="GC-Awarded CMR Lost" Or "GC-
Awarded CMR Awarded",[tblBids_BidAmount],0))

The code works fine but it sums the Bid Amount for all
records. I only want to include records that meet the
criteria in the IIF statement in the calculation.

Any comments or suggestions would be appreciated

Brennan
 
Brennan:

The problem with your statement is this:

Access is (will) only evaluating the value of tblBids-Status for the last
detail record it output. If that value equalled either of your test values
then you get a sum.

To solve the problem, create a hidden control on your report, lets call it
txtBids which contains the control source:

IIf([tblBids_Status]="GC-Awarded CMR Lost" Or _
"GC-Awarded CMR Awarded",[tblBids_BidAmount],0)

This will evaluate the status for each detail line of the report giving you
a value or 0

Then in the footer where you have the sum, simply have your control source =
Sum(txtBids)
 
Brennan said:
I have a report that has a text box used to sum some
information in a text box. The following code is present
in the text box:

=Sum(IIf([tblBids_Status]="GC-Awarded CMR Lost" Or "GC-
Awarded CMR Awarded",[tblBids_BidAmount],0))

The code works fine but it sums the Bid Amount for all
records. I only want to include records that meet the
criteria in the IIF statement in the calculation.
Click to expand...

Each part of the Or operation must have a complete
condition, you left out the field name in the second part.

=Sum(IIf([tblBids_Status]="GC-Awarded CMR Lost" Or
[tblBids_Status]="GC-Awarded CMR Awarded",
[tblBids_BidAmount], 0))
 
SA said:
To solve the problem, create a hidden control on your report, lets call it
txtBids which contains the control source:

IIf([tblBids_Status]="GC-Awarded CMR Lost" Or _
"GC-Awarded CMR Awarded",[tblBids_BidAmount],0)

This will evaluate the status for each detail line of the report giving you
a value or 0

Then in the footer where you have the sum, simply have your control source =
Sum(txtBids)
Click to expand...

I know you know this Steve, but the Sum function only
operates on record source fields, it will not work with
controls. If the dum and the IIf (with a corrected
condition) are to be separated, then the IIf should be in
the query, not in a control.
 
Back
Top