Sum Expression in Report

[pgmr2be]

I want to sum only the values that = certain criteria. For example I have a text field "TypeOfPump" which can be Pump1,Pump2,etc. I also have number field "FlowRate" which can be 20,50,200 etc. I want to sum all of the fields that contain Pump1 only. I can sum with no problem, just when I try to use criteria. Here is my expression that I used for the control source in a text box on the report, it turns the value 0. =IIf(Sum([FlowRate]),([TypeOfPump])="Pump1"). I pretty sure what I want to do is possible. If anyone has any suggestions, it would be greatly appreciated!

 

[Axis]

You have the sections of the Iff function transposed. It's Iif(expression,truepart,falsepart). In your example it would be =IIf([TypeOfPump]="Pump1",Sum([FlowRate])).

However, assuming these field names are also the name of fields in your table you'd be better served using a DSum function in the AfterUpdate property of your TypeofPump field. Example:

TypeofPump.AfterUpdate
dim strPump as String
strPump = Me!TypeofPump
DSum("[Flowrate]","[YourTableName]","[TypeofPump]= """ & strPump & """")

This will sum the flowrate for any type of pump that is chosen.

 

[pgmr2be]

Thanks for taking time to answer my question, but I couldn't get it to work. I have my text box in the Report Footer, and my report prompts for criteria driven by a query, thats why I don't think I could use the DSum like you suggested. When I run the report I get no sums at all. Some of the fields are blank so I tried using the IsNull and got a sum, but it was of all pumps, not the one I used in the expression. Here is what I tried. "=IIf(IsNull([TypeoFPump]="Pump1"),"",Sum([FlowRate]))". This seems like it would be easy, but it sure is frustrating me. Thanks again for you time.