Using DSUM Based on Multiple Criteria

[IJOC]

I am trying to sum based on multiple criteria.

I want to be able to have a calculated sum in one field that can automatically calculate the dollar amount of all records in a transaction that the ysnSP field = "YES".

So I created a field in my report and added this below. However it did not display anything when running the report.

=DSum("currBV * intOutCardQty","qrptTransactionsOut","ysnSP = yes" & [pkTransID]=[pkTransID])

Any help would be great!
Thanks
-Pat

 

[PWise]

how about

=DSum("currBV * intOutCardQty","qrptTransactionsOut","ysnSP = yes and  [pkTransID]=" & [pkTransID])

 

[IJOC]

That did it. All too simple. Thanks again

 

[dhookom]

If you are summing values from records in the report's record source, you should probably not use DSum() since it slows down your report. If your reports record source is qrptTransactionsOut and the text box is in a header or footer section of pkTransID and ysnSP is a yes/no field then try:

  =Sum(Abs(ysnSP) * currBV * intOutCardQty)

Duane
Hook'D on Access
MS Access MVP