<Solved>Count how many records display 0

[tareyj8569]

Hello everyone,



First off thank you for your time/help.

I am trying to count the records in Total Column that display 0 from a Sub Form into my Main Form into an unbound box.
Currently I have

Main Form

unbound box - (code to pull count of 0's) which in this case should display 2 since there is total of 2 employees with 0 across in the Total Column


Sub Form - which data displays just as you see below
In my Sub Form the Name, Test 1, Test 2 are fields from a table.
Total is an unbound field that is =[Test 1] + [Test 2]

Name	Test 1	Test 2	Total
Tom	1	0	1
Betty	1	1	2
Kim	0	0	0
Hank	2	1	3
Tim	0	0	0

[June7]

Textbox in subform footer:

=Sum(IIf([Test 1] + [Test 2] = 0, 1, 0))

If subform is in datasheet, a textbox on main form can reference the subform textbox.

=[subform container name].Form.textboxname

[tareyj8569]

very nice, are you allowed to do more than one =SUM like that or just one per subform? a sum for the tests and if i wanted to do a sum on the names could that be done or no?

[tareyj8569]

This worked perfectly btw, was just curious about below.

[June7]

Do as many as you want. Just means more textboxes.

Curious about what below?

[tareyj8569]

When i posted that second comment I should of said above not below=P... when i try to do more than one sumif i get errors throughout my main form, but if i do one sumif no issues, maybe i am trying to code it wrong now that i look at it.. how would i change it so it would count any of them that is greater than 0? =Sum(IIf([Test 1] + [Test 2] = >0, 1, 0)) is what i tried but did not seem to do it correct.. so from example above i want it to say 3 have numbers

[June7]

Post the expressions you attempted. Aggregate functions (Sum, Count, Avg, etc) must refer to fields of form RecordSource not textboxes.

[tareyj8569]

Main Form
(This is text 1)
=[Subform].[Form]![text2]

Subform
(This is Text2) in the footer of subform
=Sum(IIf([Test 1] + [Test 2] = 0, 1, 0))

This works no problem

Than if I try to duplicate the same thing
(This is text 3)
=[Subform].[Form]![text4]

Subform
(This is Text4)
=Sum(IIf([Test 1] + [Test 2] = >0, 1, 0))

I get an #errors on my countifs in the main form but my initial sum works fine.
But if i only have one Sumif in the subfom everything is fine. (even if i just copy and paste the same working one twice it will freak out the main form)
Hope i didnt make things to complicated

[tareyj8569]

Oh, Jeeze disregard the #Errors, when i did a copy paste it pasted a textbox down in the footer of the form and was messing everything.
So only issue now is to get it to count how many are greater than 0 instead of how many are 0 which is working. For some reason I cannot figure out how to just count how many = greater than 0. I keep getting a total of 6 instead of 3 as in count

[tareyj8569]

wooo got it, =Sum(IIf([Test 1] + [Test 2] = >0, 1, 0)) had to get rid of the 2nd = so =Sum(IIf([Test 1] + [Test 2] >0, 1, 0))
thanks for time and help