Calculated Field in a Form-formula problem

[RBoshears]

I have a form with an underlying query. I am attempting to generate several calculated controls based on fields within the query. Most fields contain either a "yes", "no", "pending", or No Resp". I am trying to count each result within a given field. My formula in the control is

=Count(IIF([field name],yes,0)). The result is always the total number of records, not just the ones with a "yes" in them. Help???

 

[jobb]

how about

=Sum(IIF([field name]="yes",1,0))

 

[RBoshears]

Nope, that generates an error. if I eliminate the 1 in the formula, it runs, but still gives me the total record count, not just the yeses.

 

[jobb]

try this..

=Count(IIF([field name]="yes",1))

 

[RBoshears]

Nope, that doesn't work either.

 

[jobb]

really? what is the result?

 

[RBoshears]

with the "" around yes, I get an #error code, without the "", the formula runs, but it is still giving me the total number of records. I have 8 records, with 7 yeses, but the formula result keeps on coming up 8.

 

[jobb]

I take it the field's a checkbox then, not a text field.

well, I give up.. it works for me!

 

[RBoshears]

not a check box, but it is a list.

 

[jobb]

either way, the formula should work..

one thing to watch out for is that the formula won't calculate if the active record has changed unless it is saved (which is done by leaving for another record, or running a macro after updating it)