Adding up checkboxes

[tac5829]

The other day I check with various people on how I might get column information added together as it relates to checkboxes. Yet, I am still having trouble making this work, please read the following. Any help is appreciated.

Example: Item A (if checked)= 33%, Item B (if checked)= 33%, Item C (if checked)= 33%. Once adding (if A & B are checked) shows that I have 66% completion on that particular project.

I was instructed to...........create a text box on a form, set the format property to 'Percent' and the 'Control source' can look like this:

=iif([ItemA]=True,0.33,0)+iif([ItemB]=True,0.33,0)+iif([ItemC]=True,0.33,0)

This will add these three items up and give you the percentage. You can also do the same in a query to based your reports on these percentages.

Yet, It isn't working properly. Any ideas?

 

[Jon K]

You can use the following expression in a query to display the percentage for each record:-

Percentage: Format(-([Item A]+[Item B]+[Item C])/3,"Percent")


Then, if you want, you can build a form based on the query.
.

 

[tac5829]

what if the items have different values; such as, Item A = 20, Item B = 30, Item C = 50 ?

They all have different values in priority of completion, but all needing added for total percent of completion for the entire task per record.

 

[raskew]

Hi -

Yes/No fields return -1 if True (checked), 0 if False (unchecked). Say you had
ChkA which would represent 20, ChkB representing 30 and ChkC representing 50. The following (adapted to your table/field names) would return the total values. As you can see, there's no need for Iif() statements. If the field is unchecked, it's already at 0

SELECT
    tblChkTest.ItemName
  , tblChkTest.ChkA
  , tblChkTest.ChkB
  , tblChkTest.ChkC
  , Abs(([ChkA]*20)+([ChkB]*30)+([ChkC]*50)) AS MyPct
FROM
   tblChkTest;

HTH - Bob