Creating a counter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I would like some help doing this. I have created a field that would hold
these values, called PLANCOUNT. I have 12 different check box fields that I
need to count and update PLANCOUNT with. Would an update statement similar to
this do the job:

UPDATE tablename
SET PLANCOUNT = **this is where I have trouble with**

The desired end result would be a number representing the number of fields
that are checked (True).

Any help would be greatly appreciated.

Thanks.
 
Are these checkboxes on a form or in a table?

In a table, use an expression like: ABS (field1 + field2 + field3 + ....).
The ABS() function is needed because 'YES' = -1 (not 1).

Drawback is if you ever add or remove a checkbox field from the table
definition, you have to manually adjust this expression. If these were in a
table that was related to the base table (the one in which these twelve
checkboxes reside in) and each row was one of those checkboxes, the SQL
could be constructed without concern for the number of checkboxes. It would
'self correct'.

If they are on a form, you'll have to write some VBA code to loop through
these checkboxes and add up the values.

Good Luck!
 
Thanks a lot, Chaim. Just the answer I needed.

Chaim said:
Are these checkboxes on a form or in a table?

In a table, use an expression like: ABS (field1 + field2 + field3 + ....).
The ABS() function is needed because 'YES' = -1 (not 1).

Drawback is if you ever add or remove a checkbox field from the table
definition, you have to manually adjust this expression. If these were in a
table that was related to the base table (the one in which these twelve
checkboxes reside in) and each row was one of those checkboxes, the SQL
could be constructed without concern for the number of checkboxes. It would
'self correct'.

If they are on a form, you'll have to write some VBA code to loop through
these checkboxes and add up the values.

Good Luck!
Click to expand...
 
Back
Top