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?

 

[FoFa]

If the value of one of your check boxes change on the form, you have to requery this control to get it to update. Or is there another issue?

 

[tac5829]

I guess I'm lost on this. I will try to be very detailed in my description.

I have 10 column headings in my query (they are checkboxes in form) that I want each to have a different value for them (Ie. 20, 5, 5, 5, 5, 5, 5, 5, 10, 10, 15, 10 (when checked)). In an additional column I'd like to be able to have a total of the 10 values (in the record), representing the total percentage complete (of the 10 columns in that record).

In the form, the checkboxes will be linked to the query with values as indicated. The form will have a textbox that will show the percentage complete for that record.

Right now, I have everthing set up, except where to place these expressions and such. Do I place them in the format and source and have the total show only in the form, or can I have it in the query as well?

Man,I hope this makes sense. hahaha

Thank again for your time and effort to help me!

 

[FoFa]

It depends on your usage. If it is a display only field, you could calculate it either n the query or on the form. But are they allowed to change one of these check boxes on this form? If so than you need to calculate it on the form so you can change it when the checkbox is updated manually. If this form does NOT allow them to change the checkboxes, than either place would work.
Is that what you were asking?

 

[tac5829]

Yes they can click and unclick the checkbox as required. And the calculated value will need to be on the form and in the query.

 

[FoFa]

Sounds like just the form (for this anyway). If they check/uncheck a box, you ened to refresh the control so it will recalculate the total. Much better than refreshing the whole query each time they update a checkbox. Now other queries may need to calulate this also depending on how you need the data. But forms/reports can do that in the form/report. Sounds like a better option for you.

 

[tac5829]

How is the question......I'm not as proficient at this as I thought.
hahaha

 

[FoFa]

I think your best bet would be to create a function. If done properly you could use it in both forms and queries. If you feed each checkbox value as a parameter, and returned a total, it would be simple, all the code contained in one place for ease of maintenance, etc.

 

[MarkK]

Here's an idea.
1) Change your yes/no fields in your table to Number->Integer
2) Noting that a check box displays as checked for all integer values except zero, bind your check boxes on your form to integers in your table.
3) On the click event of a check box on a form, set the value of the underlying integer field to the value you want it to contain. Here's the code, assuming your checkbox AND your field name are chkData.

Private Sub chkData_Click()
[COLOR="Green"]  'if user has set the check box, then reset the field value to 20[/COLOR]
  If Me.chkData Then Me.chkData = 20
[COLOR="Green"]  'if the user has cleared the check box, then the value is zero, which is correct[/COLOR]
End Sub

Write a code block like that for each check box assigning the correct value for the field it represents, and your data is stored in your table and available to be queried.