How do I count "true" occurances?

[dab1477]

I've created an audit form where I simply want the Auditor to select or click on a Yes/No box for a series of 16 quesstions. I then want to count the TRUE or YES conditions for all 16 questions within a text box on the form. This gives feedback as to the score for that audit.

Ex: Auditor will complete the audit questions as follows:

Question A-1: Yes (or True) - Yes/No box was selected
Question A-2: Yes (or True) - Yes/No box was selected
Question A-3: NO (or False) - Yes/No box was NOT selected

The score for the above would be 2 divided by 3 (True occurrances/Total questions or 16) or 66. I want this score (66) to show on the form once audit is complete.

There are a total of 16 questions on the form. I would like code to count the TRUE for all 16 questions and place in a box on the form. I don't know how to get started. I think I want something like: If A-1.enabled = True, then A-1 = 1, elseif A-1.enabled = False, then False = 0....etc and then Sum? How would I begin to set this up? Am I doing this the right way? If I do a query, how do I count True occurrances? I can count records, but not occurrances within each record.

I hope you can help a neophyte! Thanks in advance.

 

[SkipVought]

hi,

What application?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Is that a UserForm?
And your "Yes/No box" - are those the CheckBoxes where you check them for Yes or un-check them (leave blank) for No?

Have fun.

---- Andy

 

[dab1477]

Yes, it is an Access 2010 userform where the Auditor clicks a checkbox for YES and leaves blank for No.

 

[dhookom]

Have you considered normalizing your table so that each question is a record rather than a field? Then you would total across records rather than across fields.

If interested in how the normalized table structure would look, check out At Your Survey.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I did it in Excel, but it should work in Access: a few checkboxes (as long as there are NO other checkboxes than the ones accepting the answers), one command button and one text box:

Option Explicit

Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim iChecked As Integer
Dim iUnChecked As Integer

For Each ctrl In Me.Controls
    If TypeOf ctrl Is MSForms.CheckBox Then
        If ctrl.Value = True Then
            iChecked = iChecked + 1
        Else
            iUnChecked = iUnChecked + 1
        End If
    End If
Next ctrl

TextBox1.Text = iChecked / iUnChecked

End Sub

Of course you need to make sure iUnChecked <> 0 because of the Division by 0 error

Have fun.

---- Andy

 

[dhookom]

Andy,
Excel isn't quite the same as Access when it comes to forms and "Text" property.

If dab1477 chooses not to normalize, the code might be something like:

Private Sub cmdCalcChecked_Click()
    Dim ctrl As Control
    Dim iChecked As Integer
    Dim iCount As Integer
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is CheckBox Then
            iCount = iCount + 1
            iChecked = iChecked + Abs(Nz(ctrl, 0))
        End If
    Next
    If iCount > 0 Then
        Me.txtPctChecked = iChecked / iCount
     Else
        Me.txtPctChecked = 0
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[dab1477]

Andy,
Below is what I have to date. I am erroring out at line 7 "If TypeOf ctrl Is Ms.Forms.CheckBox Then"
I get a compile error "User defined type not defined". I tried to change MS.Forms to ME.Forms, but that did not work. I added the command button (name is Command84). The code is part of the command button. I named the text box as ScoreCalc. It is unbound. And the Checkboxes (a total of 16) seem in order. They are the ONLY items on the form having input - other than memo boxes. I have uploaded a copy of my form for clarity.
I understand what you're doing, but something simple is making me brain dead! Please continue my education and set me on the right path! Thanks


Option Explicit
Private Sub Command84_Click()
Dim ctrl As Control
Dim iChecked As Integer
Dim iUnChecked As Integer

For Each ctrl In Me.Controls
If TypeOf ctrl Is Ms.Forms.CheckBox Then
If ctrl.Value = True Then
iChecked = iChecked + 1
Else
iUnChecked = iUnChecked + 1
End If
End If
Next ctrl

ScoreCalc.Text = iChecked / iUnChecked

End Sub

 

[dab1477]

Thanks Dhookoom. I did not normalize, therefore your method worked. I'm a happy camper. Thanks to one and all for the path to enlightenment.