459,442 Members | 1,316 Online
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

# Access Sum Function Criteria

 P: 33 Hello, I am trying to sum a column on a subform named Cost of New if another column named Final = any of the values listed in my code. However, it is summing the Cost of New column regardless of the values of Final. It is summing for all values. What do I need to modify to make this correct or should I do it another way? Expand|Select|Wrap|Line Numbers =IIf([Final]="RPR-RPR & RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Sum([Cost Of New]),Null) Thanks! Rhonda Jun 16 '15 #1

I'm not exactly sure what you are trying to do, but typically the Sum() goes on the outside of an expression:
Expand|Select|Wrap|Line Numbers
1. =Sum(IIf([Final]="RPR-RPR & RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Nz([Cost Of New], 0),0))
2.
Also, Sum doesn't always work the way you expect with Nulls, so setting them to 0 is usually (not always) better.

If this isn't what you are after, please let us know a little more of what you are trying to do.

2 Replies

 Expert 100+ P: 1,107 I'm not exactly sure what you are trying to do, but typically the Sum() goes on the outside of an expression: Expand|Select|Wrap|Line Numbers =Sum(IIf([Final]="RPR-RPR & RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Nz([Cost Of New], 0),0))   Also, Sum doesn't always work the way you expect with Nulls, so setting them to 0 is usually (not always) better. If this isn't what you are after, please let us know a little more of what you are trying to do. Jun 16 '15 #2

 P: 33 That is what I am looking for, thank you! Jun 16 '15 #3