Count(IIF... not working 2

[uscctechnician]

I am stumped on this.

I have a Table with a few fields that are set up as yes/No and I am using a value list of Yes and No. On my form, I am using a combobox and the person using the form can select either Yes or No.

In the footer of the form, I have a text box set up to give me the count of how many "No" were entered.

So, the field in the table is called "Form Complete". So my control source for the text box I want to show the count is:
=Count(IIf([Form Complete]="No",1))

For some reason, all I get on this form is ###error

The strange thing is I have 4 additional forms which each accesses data off of a different table, but they are all working using the same code.

What am I missing?

thank you for your help!!

 

[PHV]

I'd try this:
=Abs(Sum([Form Complete]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, I meant this:
=Sum(IIf([Form Complete],0,1))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[uscctechnician]

Hey PHV,

Thanks for the quick reply.

 

[dhookom]

If you want to count the No values of a Yes/No field, use:

=Sum([Your YN Field Name]+1)

Yes/No fields are not strings/text. They are values of either or -1 or 0 (possibly null also).


Duane
Hook'D on Access
MS Access MVP

 

[uscctechnician]

Dhookom,

Thanks for the reply. But doesn't that actually give me the total number of entries for that field name? If I have 5 yes records, and 4 no records, I am looking for the answer of "4", not a sum of the field which would be 9.

Either way, I finally got it to work after playing with it some more but as with PHV, thank you for your reply. thats what I love about this forum. Anyone needs help and everyone is willing to help.

regards!
Richard

 

[dhookom]

My solution sums correctly. If the value of the field is Yes/True/-1 then adding 1 makes it a 0. Just try it.

Duane
Hook'D on Access
MS Access MVP

 

[uscctechnician]

Ahhh, I see what you are saying... will it work if the field in the table is a text field with a value list of Yes/No or does this only work if the field is a yes/No type in the table?

 

[dhookom]

Sum() will only work with numeric values.

Duane
Hook'D on Access
MS Access MVP