Totaling Yes/No Data Type (Access 97 SR-2)

[kens99]

I have a field that is a Yes/No checkbox and I would like to calculate the total of boxes checked Yes.

[HansV]

Say that your field is named Test. In a Totals query, you can use the expression Abs(Sum([Test])) to count the number of Yes values. On a form, you can put a text box in the form footer section with control source

=Abs(Sum([Test]))

Explanation: True corresponds to -1 and False to 0. When you sum the field, each Yes contributes -1; taking the absolute value removes the minus.

[kens99]

Thank you Hans V for your response to my posting last week! I'm still having problems with my query, so let me expand a little more. I need to create a report that would total several Yes/No fields like LvnWill (living will), MealsOnWhls (meals on wheels), LifeLnRef (Lifeline Referral) that were check yes, based on a start date and a end date or between dates of a DDate (Discharge Date) field.

[HansV]

Will your report only include records with DDate between the start and end dates, or will it include other records too? If the former, you could put text boxes in the report footer section with control source

=Abs(Sum([LvnWill]))
=Abs(Sum([MealsOnWhls]))
=Abs(Sum([LifeLnRef]))

[kens99]

Hi Hans

I created a query using a Between statement based on the DDate field, I also included in the query 12 Yes/No fields. I then created a new report with the above query linked to it and included your suggestion of adding a text box for each of the Yes/No fields in the Page Footer with =Abs(Sum([field name])) in the Control Source. When I run the report I get '#Error' for the response.

[HansV]

As I wrote in my previous reply, you should place the text boxes in the report footer, not in the page footer. If you open the report in design view, you can simply select the text boxes and drag them from the page footer to the report footer.