How to Count Check Boxes that are checked Yes in an Access 2003 Report

Posted on 2008-11-03
Last Modified: 2013-11-28
I have a table that has 10 different check boxes for various options.  I need to create a report that shows the total number of times each check box is selected.    Some records can have multiple check boxes selected  and other records might not have any check boxes selected.  
(Examples of field names for the check boxes are, patient_reffer_family, patient_reffer_insurance, patient_reffer_friend)

I also want to show a total number of records that have at least one of the available check boxes selected.   I want to use that number to figure out percentages based on the totals from the individual check box totals.

I beleive I need to use the count feature, but I am not sure how to code this properly  If possible, examples of how to use this code would very helpful.
Question by:anctech
  • 5
  • 2
LVL 44

Expert Comment

ID: 22868763
A checkbox has a real value of either -1 or 0

to count by record, using dummy 'short' names

SELECT-(cb1+cb2+cb3+cb4+cb5+cb6+cb7+cb8+cb9+cb10) as CountofCheckboxes FROM myTable:

LVL 44

Expert Comment

ID: 22868776
There should be a space:

SELECT -(cb1+cb2+cb3+cb4+cb5+cb6+cb7+cb8+cb9+cb10) as CountofCheckboxes FROM myTable:

Author Comment

ID: 22869045
Thanks, but you lost me.    That looks like it would count all the checkboxes.  Also, where would that statement go.

How would I make a control directly on the report?
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

LVL 44

Accepted Solution

GRayL earned 500 total points
ID: 22869677
Put it in the control source of a text box, provided the report is bound to the table containing the checkboxes amongst other things:  

= -(cb1+cb2+...+cb10)

Remember, this is reversing the sign of the sum (not count) of the value of all the check boxes, some of which are 0 (False) and some are -1 (True).  You do not want to use the Count() function.  In fact you are not using the Sum() function either - you are simply adding 10 fields, each of which has a value of 0 or -1, and changing the sign of that sum.  Get it?

Author Comment

ID: 22869839
You are one step ahead I think.  I need to get a total for each individual check box first.   After I get that, then I can use your example to get a grand total.

Here is what I tried to get a count of just one single check box field.

I tried this  to get a count for one of the check boxes and it didn't work.
  Sum -([patient_reffer_insurance])

If I use   Sum ([patient_reffer_insurance]) without the - sign, I get a result of -7.00

I need this to be a postive number
LVL 44

Expert Comment

ID: 22870696
You would use -Sum(patient_refer_insurance)  - spelling reffer?  Thanks, glad to help.
LVL 44

Expert Comment

ID: 22870719
Don't like the B though.  Why did you close the question when you still had one?

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now