By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,316 Online
Bytes IT Community
+ Ask a Question
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
  1. =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

✓ answered by jforbes

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.

Share this Question
Share on Google+
2 Replies


jforbes
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
  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.
Jun 16 '15 #2

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

Post your reply

Sign in to post your reply or Sign up for a free account.