Counts on Yes No fields

[Peter Whitfield]

I have a form with 45 different Yes/No fields in, I need to produce totals of those selected. I tried a bog standard totals query but this just gave the number of records in the form regardless of the state of the relevant checkbox. Do I need to do 45 queries each one stating criteria of Yes? Any help would be appreciated.

 

[Pat Hartman]

If you have 45 fields that hold the same type of data and that you want to analyze as a set, you have probably NOT designed the table properly. You probably should have made a separate table that would support the 1-to-many relationship.

In any event, a totals query should do the trick. You need to use the sum() function. The True value is -1 so you will end up with a negative number. You can use the Abs() function to convert it to positive.

Select Abs(Sum(Fld1)) As SumFld1, Abs(Sum(Fld2)) As SumFld2, ....
From YourTable;

 

[jwindon]

This will do the trick Peter:

Dim currctl As Integer
Dim numctls As Integer
Dim ctl As Control
Dim TotalYes As Integer
Dim TotalNo As Integer

numctls = Me.Count


For currctl = 1 To numctls - 1
Set ctl = Me.Count(currctl)

If ctl.ControlType = acCheckBox Then

If ctl.Value = -1 Then
TotalYes = TotalYes + 1
Else
TotalNo = TotalNo + 1
End If

End If

Next currctl

MsgBox "Total Yes = " & Str(TotalYes) & " Total No = " & Str(TotalNo)

HTH

 

[Peter Whitfield]

Cheers guys,

you're both stars!!