Iff Statement Meeting 2 Conditions

[newbie]

I am trying to create a count function in the report footer. My IIf statements where only 1 piece of criteria needs to be met are working great. I am using:



=Count(IIf([ControlName]="Yes",0))

I need to create an IIf statement that will count only if 2 piecec of criteria are met. I have tried a few things but I keep getting an error when I run the report that the expression is either typed incorrectly or is too complex. I have listed a few of the options I have tried below. Is there a way to fenagle one of these so that I can recieve a count of records meeting both criteria?

=Count(IIf([Control 1]="Yes" & [Control 2]="Yes",0))
=Count(IIf([Control 1] & [Control 2]="Yes",0))
=Count(IIf([Control 1]="Yes", Count(IIF([Control 2]="Yes",0)),0) this one returned a cannot have aggregate function error.
=IIf([Control 1]="Yes",Count(IIf([Control 2]="Yes",0)),0)

Thanks

[RuralGuy]

Try:
=Count(IIf([Control 1]="Yes" AND [Control 2]="Yes",1,0))

[newbie]

No, I get the same message that either my expression is typed incorreclty or it is too complex.

[RuralGuy]

If you leave off the Count() does it work?

[newbie]

That eliminated the error message, but just returned a result of 0 when there are 6 records matching both criteria.

[RuralGuy]

Are [Control 1] and [Control 2] both Text fields?

[newbie]

Yes, both control 1 and control two are Yes/No text fields. I have to pull the count from the report itself and not the underlying query, because the query has parameters. The report itself pulls back the exact filtered info I want counted, just can't seem to get there.

[RuralGuy]

TextFields and Yes/No fields are not the same. I believe the Yes/No is actually just an Integer forld with either 0 (NO) or -1 (YES) as a value. Try this:
=IIf([Control 1] = Yes AND [Control 2] = Yes,1,0)
...and see what happens. Note - No quotes. You could also try
=IIf([Control 1] AND [Control 2] ,1,0)
...and you should get the same results.

[newbie]

Still no luck. I tried with the numeric values as well. It is returning an all record count.

[RuralGuy]

I was just trying to find the right criteria first without the count. When I do what you are doing in a report I usually put an invisible control in the detail section with an IIF() to give me a 1 or 0. Then I just Sum() in the footer.

[newbie]

OMG!!!! I finally got it.

I created an unbound text in my report footer and set the control source to:
=Sum(IIf([control 1]="Yes" And [control 2]=Yes,1,0))

I feel like I just ran a marathon!! One issue down, several more to go.

[RuralGuy]

Excellent! Thanks for posting back with your success.