Sum IIf items being true (1 Viewer)

swingline

New member
Local time
Today, 21:05
Joined
Feb 18, 2020
Messages
2
I'm looking to Sum IIF with multiple items being true. I can't seem to figure it out

Sum(IIf(([TD]![COMPLETE]="YES") And ([TD]![NOAC]>1),1,0))
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 13:05
Joined
Feb 28, 2001
Messages
16,261
Since you are only summing 1s and 0s, do a COUNT(*) with a WHERE clause that includes [COMPLETE]="YES" AND [NOAC]>1.
 

plog

Banishment Pending
Local time
Today, 13:05
Joined
May 11, 2011
Messages
9,642
Looks syntatically correct, hows it not working?

The way to find out a thing with multiple parts is failing is to isolate the parts.

What does this query return do?


Code:
SELECT COMPLETE, Iif(Complete="YES", 1) AS Complete_Test
FROM TD
Then add 2 fields to test NOAC, then add a field with just the IIF that tests them both, etc, etc.
 

cheekybuddha

Registered User.
Local time
Today, 19:05
Joined
Jul 21, 2014
Messages
196
Is [COMPLETE] a string datatype or a boolean?

Try:
Code:
Sum(IIf(([TD]![COMPLETE] = True) And ([TD]![NOAC] > 1), 1, 0))
@plog: you're missing the FALSE portion of your IIf()
 

cheekybuddha

Registered User.
Local time
Today, 19:05
Joined
Jul 21, 2014
Messages
196
Code:
?IIf(True, "Yay")
Code:
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

Argument not optional
---------------------------
OK   Help   
---------------------------
 

plog

Banishment Pending
Local time
Today, 13:05
Joined
May 11, 2011
Messages
9,642
I made a TD table with a Complete field, added a Yes and No record and ran my query:

Code:
SELECT COMPLETE, Iif(Complete="YES", 1) AS Complete_Test
FROM TD
Complete, Complete_Test
YES, 1
X,
 

cheekybuddha

Registered User.
Local time
Today, 19:05
Joined
Jul 21, 2014
Messages
196
Fair enough, I didn't test in a table. 👍

Good to learn you don't need the FALSE part when used in a query.
 

swingline

New member
Local time
Today, 21:05
Joined
Feb 18, 2020
Messages
2
Is [COMPLETE] a string datatype or a boolean?

Try:
Code:
Sum(IIf(([TD]![COMPLETE] = True) And ([TD]![NOAC] > 1), 1, 0))
@plog: you're missing the FALSE portion of your IIf()
It is a String and this worked out wonderfully. Thank you for the help
 

plog

Banishment Pending
Local time
Today, 13:05
Joined
May 11, 2011
Messages
9,642
It is a String and this worked out wonderfully.
Did it?

Code:
Sum(IIf(([TD]![COMPLETE] = True) ...
In the above equation, if [COMPLETE] is a string and its value is "No" or "False" it will evaluate to True. You need to do the right type of comparison on your fields.

If [COMPLETE] is a string you need to set the right side ="Yes" or ="True". If it is a Yes/No datatype then you can use the experession you posted.
 

cheekybuddha

Registered User.
Local time
Today, 19:05
Joined
Jul 21, 2014
Messages
196
I agree with plog.

Double-check your results - if [COMPLETE] is a string datatype then the expression will return 1 as long as [COMPLETE] is not empty.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom