counting true fields

[Guest]

I have a field that is capturing as a true/false field. I have tried every
suggested way of counting these fields to no avail. I have tried
sum(Abs([fieldname])), DCount([fieldname]), Abs(Sum([fieldname]="Yes")),
sum(IIf(fieldname,1,0)). Nothing works, I get either a -1 or #Error. I am
trying to do this calculation in a form from a table that has the field. I
will also need to add a couple of AND and OR statements to the original count
once I get that working. Any thoughts?

 

[Joseph Meehan]

I have a field that is capturing as a true/false field. I have tried
every suggested way of counting these fields to no avail. I have tried
sum(Abs([fieldname])), DCount([fieldname]),
Abs(Sum([fieldname]="Yes")), sum(IIf(fieldname,1,0)). Nothing works,
I get either a -1 or #Error. I am trying to do this calculation in a
form from a table that has the field. I will also need to add a
couple of AND and OR statements to the original count once I get that
working. Any thoughts?

Hint, in Access a yes is -1 and a no is 0.

 

[John Vinson]

I have a field that is capturing as a true/false field. I have tried every
suggested way of counting these fields to no avail. I have tried
sum(Abs([fieldname])), DCount([fieldname]), Abs(Sum([fieldname]="Yes")),
sum(IIf(fieldname,1,0)). Nothing works, I get either a -1 or #Error. I am
trying to do this calculation in a form from a table that has the field. I
will also need to add a couple of AND and OR statements to the original count
once I get that working. Any thoughts?

Are you counting the number of True values in a single field, across
multiple records? or are you trying to count the number of True values
in a bunch of yes/no fields in a single record?

Hopefully you have a properly normalized design, and the first option
is true. If so, try putting a textbox on the Form Footer - it *won't*
work in the form's Detail section - with a Control Source property of

= Abs(Sum([fieldname]))

For more complex expressions, you may need to use

= Sum(IIF(<some condition here>, 1, 0))

where the condition is a logical expression using the field which
evaluates to either true or false. Again, this sum must be done in the
form footer.

John W. Vinson[MVP]

 

[Guest]

I know that but I was not getting the correct response. I had more than one
true record that met my criteria - how do I know that with just a -1.

I have a field that is capturing as a true/false field. I have tried
every suggested way of counting these fields to no avail. I have tried
sum(Abs([fieldname])), DCount([fieldname]),
Abs(Sum([fieldname]="Yes")), sum(IIf(fieldname,1,0)). Nothing works,
I get either a -1 or #Error. I am trying to do this calculation in a
form from a table that has the field. I will also need to add a
couple of AND and OR statements to the original count once I get that
working. Any thoughts?

Hint, in Access a yes is -1 and a no is 0.

 

[Guest]

I am counting a single field across multiple records. Someone answers yes to
a question on my form and there are multiple questions. I collect the data I
want to report on into a separate table. I am using a label control to build
my count. I am working in the Page Header section. I have tried your formula
and the response I get is #Error. I have done counts in the Page Header in
other reports and they work correctly. I am just having a heck of a time with
the true field.

I have a field that is capturing as a true/false field. I have tried every
suggested way of counting these fields to no avail. I have tried
sum(Abs([fieldname])), DCount([fieldname]), Abs(Sum([fieldname]="Yes")),
sum(IIf(fieldname,1,0)). Nothing works, I get either a -1 or #Error. I am
trying to do this calculation in a form from a table that has the field. I
will also need to add a couple of AND and OR statements to the original count
once I get that working. Any thoughts?

Are you counting the number of True values in a single field, across
multiple records? or are you trying to count the number of True values
in a bunch of yes/no fields in a single record?

Hopefully you have a properly normalized design, and the first option
is true. If so, try putting a textbox on the Form Footer - it *won't*
work in the form's Detail section - with a Control Source property of

= Abs(Sum([fieldname]))

For more complex expressions, you may need to use

= Sum(IIF(<some condition here>, 1, 0))

where the condition is a logical expression using the field which
evaluates to either true or false. Again, this sum must be done in the
form footer.

John W. Vinson[MVP]

 

[John Vinson]

I am counting a single field across multiple records. Someone answers yes to
a question on my form and there are multiple questions. I collect the data I
want to report on into a separate table. I am using a label control to build
my count. I am working in the Page Header section.

That's the problem< I'm guessing. Use the ReportHeader instead, or a
Section header (using the report's Sorting and Grouping dialog); or
else use a Textbox with a control source such as

= - DSum("[YesNoField]", "[queryname]", "<optional criteria>")

The Page Header doesn't let you sum values - only Report or Section
headers/footers.

John W. Vinson[MVP]