Sum IIF Function

[Vergy39]

I am trying to write a query that will sum an item with 2 criterias. I have a database that has a table called tblIssues with columns that have IssueType and FBCS. IssueType is a combo box with 3 choices, 1=true Pres, 2= Verbal, 3= CRL. The FBCS column is a Yes/No box. I need to sum the number of TruePres (1) issues that are tagged FBCS (yes). I can sum the True pres issues like this:

TruePres: Sum(IIF([tbleIssues].[IssueType]=1,1,0))

But when I add the 2nd Criteria (the FBCS part) I get errors. Any suggestions on how I can add this 2nd criteria to sum. I know I can create more queries and then add them together, but would like to simplify this. Any assistance is greatly appreciated.

Thanks
David V

 

[pbaldy]

Try

Sum(IIF([tbleIssues].[IssueType]=1 AND FBCS = -1,1,0))

 

[Vergy39]

Try

Sum(IIF([tbleIssues].[IssueType]=1 AND FBCS = -1,1,0))

Perfect. I cannot believe it was that easy. I guess that is why I am a rookie and you are a pro. You are the best.

 

[pbaldy]

Happy to help. Years of banging my head against the wall has allowed some knowledge to seep in, but there's still more I don't know than what I do know.

 

[HankLewis]

Can you assist me with summing a field based on two qualifiers from other fields? For example Sum if '2014 Rating' = 3 and 'Division Chgs to Merit %' = 1.00% then sum 'Division Merit Dollars Adjustment'.

I'm getting a syntax error when running this:

Cost Detail: Sum(IIf([2014 Rating]=3 And [Division Chgs to Merit %] = '1.00%',[Division Merit Dollars Adjustment],0))

Afterwards I would need to add OR statements with other Ratings and Merit %s

Thanks,
Hank

 

[pbaldy]

You're testing for the literal text "1.00%". Is that appropriate, or should it be a numeric test for 1 or .01?

 

[vbaInet]

Am I allowed to make a little contribution here?

This

Sum([tbleIssues].[IssueType]=1 AND FBCS = -1) * -1

... is equivalent to this

Sum(IIF([tbleIssues].[IssueType]=1 AND FBCS = -1,1,0))

But the former will perform better.

I'm out!

 

[HankLewis]

My script is running with no errors now, thank you! How do I get each row that match my criteria to Sum a total (for all that match) on ONE row?

Expr1: Sum(IIf([2014 Rating]=3 And [Division Chgs to Merit %]="2.75",[Division Merit Dollars Adjustment],0))

So if I have three rows that met my criteria I only want one total.

Thanks,
Hank