Quick question about Count expression

[Monroeski]

I tried doing a search for this, but I think the terms I need to use are too generic to come up with anything useful.

Basically, I have a yes/no field in a query that is the record source for a sub form. I am trying to get one text box to show the number of Yes', and another one to show the number of No's.

On the Text Box's control source, I assumed that since you can type "=Count([FieldName])" to get a regular count, you could type "=Count([FieldName]=0)" or "=Count([FieldName]=True)" or something like that to pick out the values you want. Unfortunately, this is not the case; all three expressions just return the total count. Is there any way to do this easily, or am I going to need to get down into the VBA?

[mlcampeau]

Count doesn't let you have a condition, but DCount does.

=DCount("[FieldName]","TableName","[FieldName]=True")

(Note that the syntax in the condition portion may be wrong...I'm still learning when to use quotes and such in this function)
You could also try something like this:

=Sum(IIf([FieldName]=True,1,0))

[Monroeski]

Got it working. I knew how to do it in code, but this is a REALLY simple little project, so I wanted to just do it right there on the form. Thanks to a few other little nuances, it would have been a lot of extra work to do this stuff in code. Plus, I just wanted to know how to do it for future reference.

If anyone cares, I was also using the count to calculate a percentage, and the expression I put as the control source ended up looking like this -

=DCount("[FieldName]","QueryName","[FieldName]=True")/Count([FieldName])

Thanks for the help.

[mlcampeau]

Got it working. I knew how to do it in code, but this is a REALLY simple little project, so I wanted to just do it right there on the form. Thanks to a few other little nuances, it would have been a lot of extra work to do this stuff in code. Plus, I just wanted to know how to do it for future reference.

If anyone cares, I was also using the count to calculate a percentage, and the expression I put as the control source ended up looking like this -

=DCount("[FieldName]","QueryName","[FieldName]=True")/Count([FieldName])

Thanks for the help.

I'm glad you got it to work! Thanks for posting your final solution!