Aggregate functions

[gary.milam72]

Ok, so I have query with the following criteria: Total Classes: Count(IIf([tblDocuments]![Red]+[tblDocuments]![blue]+[tblDocuments]![green]=Yes,1,Null)). This query has some proprietary info in it so I can't show the real words so I used colors. but anyway, I want it to count the number of classes, except when there is a closed date for the class, I don't want it to count the closed class. Help....

 

[CJ_London]

without context can't help - as written, it is very unlikely the three fields would add up to yes (which is -1) - and where does the closed date come into this?

Finally what exactly is your question?

 

[gary.milam72]

Here is a sample of what I'm trying to do.

 

[Cronk]

If you want to know the total number of 'Yes' values across all fields use
TotalClasses: -count([Red] + [Blue]+Green)
Criteria: DateDestroyed Is Null
In your sample database, the output would be 3

If you want to know the number of records where all 3 fields are ticked use
TotalClasses: -Count(Red and blue and Green)
Criteria: DateDestroyed Is Null
In your sample database, the output would be 0

 

[CJ_London]

still not clear to me what you are trying to do - describe it in simple English - something like

'I want to count all records where red, green and blue fields are true and dateDestroyed has not been completed'

or

'I want to count all records where only one of the red, green or blue fields is true and dateDestroyed has not been completed'