sum/count up text in multiple fields in table

[megatronixs]

Hi all,

I have a form that is built from a table that contains multiple questions.
each question is a field. I would like to count or sum up how many questions with a "yes" where filled in and have this show up in a textbox in the form.
I have now in the form question_1 till question_11 and the field name where I need to have the sum/count is called "sum_of_points"
can this be done on the form textbox directly or does it need to be a query?

Greetings.

 

[sneuberg]

If these questions are all on the same form then I don't think you can do this with a query. You could create a function like the following and put in in a standard module.

Public Function CountQuestions(ByRef frm As Form) As Long

Dim ctrl As Control
For Each ctrl In frm.Controls
    If ctrl.ControlType = acCheckBox Then
        If Left(ctrl.Name, 9) = "question_" Then
            If ctrl Then
                CountQuestions = CountQuestions + 1
            End If
        End If
    End If
Next ctrl

End Function

Then in the control source of the textbox that would have the count you would put

=CountQuestions([Forms]![TheNameOfTheForm])

You can see this demonstrated in the attached database where the form name is Form1 the control source is set to:

=CountQuestions([Forms]![Form1])

I set the default values of the checkboxes to 0 to avoid Null errors but if your checkboxes are bound to fields you probably won't have to do this.

If these question where in separate records this could be done more easily with DCount. I suggest looking at your structure to see if it is in normal form.

 

[megatronixs]

Hi,
I was looking at it and it works with the check boxes.
I was wondering if it could be done with a text box where the value could be yes, or no.
I will also need to count how many "no" in the form.

Greetings.

 

[sneuberg]

Below is the function from the attached database that counts "no"s in textboxes and combo boxes.

Public Function CountQuestions(ByRef frm As Form) As Long

Dim ctrl As Control
For Each ctrl In frm.Controls
    If ctrl.ControlType = acTextBox Or acComboBox Then
        If Left(ctrl.Name, 9) = "question_" Then
            If Not IsNull(ctrl) Then
                If ctrl = "no" Then
                    CountQuestions = CountQuestions + 1
                End If
            End If
        End If
    End If
Next ctrl

End Function

 

[megatronixs]

Hi,
It is working nice now

BIG THANKS for this.

Greetings.

 

[sneuberg]

I was just thinking that if someone type in " no" with a space before it it wouldn't be counted. You could make sure it's count by using the trim function, i,e, change the line

 If ctrl = "no" Then

to

 If Trim(ctrl) = "no" Then

but you can also do that sort of thing in the afterupdate events of the textboxes and while you are at in make sure the "no"s are all formatted the same by using StrConv. In the example below, " no", "NO", "nO" would all be converted to "No"

Private Sub question_4_AfterUpdate()
    
Me.question_4 = StrConv(Trim(Me.question_4), vbProperCase)
    
End Sub

That way they wouldn't be saved with embedded white space. Of course StrConv has other options.

 

[Galaxiom]

A better structure would have had the questions and associated answers as just two fields with separate records for each question. Then summing the results would be performed entirely by the database engine without any code.

The questions and answers would be displayed in a subform.

 

[megatronixs]

Hi Snueberg, I ended up using a combobox with a choice of Yes and No. This way no one will mess it up.

Greetings.