Counting Yes/No Data Fields

Access offers an unusual data type named Yes/No. It is really a Boolean data type and stores one of two values: -1 or 0. The field displays a small check box. When checked, it equals Yes, or -1. When not checked, it equals No, or 0.

In earlier versions of Access, a simple Sum function was used in a query expression to count the number of Yes responses in the form
Yes: Sum(field).

The function returned a negative value, as all the fields contain only -1 or 0. If the negative value is a problem, you can wrap the expression in an Abs() function in the form
Yes: Abs(sum(field)).

Access 2000 can handle this too, but you might have to alter the query's SQL expression. If you receive an error message about trying to group on fields with the asterisk character, open the query's SQL window by clicking SQL from the View button and delete the * character and the comma right before the asterisk in your SQL clause.

If you want to count the No fields, use a query expression in the form
No: Count(field) + sum(field).
This simple expression will count the number of fields and then add the absolute value of just the yes fields. For instance, if you have three records and two contain Yes values and one contains a No value, this expression would evaluate as 3 + -2, which equals 1.

This tip is extracted from Tipworld. For more tips on many other things visit their site called TipWorld.
home button   Just click on the button for the Tiproom's Home Page.
Date of last revision: 17 August 2001.