Sum IIf items being true (1 Viewer)

swingline

Member
Local time
Today, 18:55
Joined
Feb 18, 2020
Messages
51
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

Immoderate Moderator
Staff member
Local time
Today, 10:55
Joined
Feb 28, 2001
Messages
26,999
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, 10:55
Joined
May 11, 2011
Messages
11,611
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

AWF VIP
Local time
Today, 15:55
Joined
Jul 21, 2014
Messages
2,237
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

AWF VIP
Local time
Today, 15:55
Joined
Jul 21, 2014
Messages
2,237
Code:
?IIf(True, "Yay")

Code:
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

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

plog

Banishment Pending
Local time
Today, 10:55
Joined
May 11, 2011
Messages
11,611
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

AWF VIP
Local time
Today, 15:55
Joined
Jul 21, 2014
Messages
2,237
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

Member
Local time
Today, 18:55
Joined
Feb 18, 2020
Messages
51
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, 10:55
Joined
May 11, 2011
Messages
11,611
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

AWF VIP
Local time
Today, 15:55
Joined
Jul 21, 2014
Messages
2,237
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

Top Bottom