Sum Check Box Value = True

[RangerFan]

I am trying to total a check box table column for all rows that are in fact checked. I am using the query wizard in this way:

Field: Column Name
Table: Table Name
Total: Sum
Sort:
Show:
Criteria: True

I am getting the correct numeric result, however the result is apprearing as a negative number. I am wondering why the result appears as -4. Should it not be a positive value? This will also cause a problem later for reporting purposes. Any help with this question would be appreciated.

 

[lespaul]

Boolean/Logic data types are stored as 0 or -1 where 0 is false and true is -1. The easiest way around it is to use the ABS function to return the absolute value of the sum.

Switch to the SQL view of the query and change:

COUNT(FieldName)

to

Count(ABS(FieldName))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[RangerFan]

Hi Leslie,

That worked like a charm, and you are a gem.

Thanks so much.

Jeff