how to calculate total trials?

[acd2]

i have a form that contains 5 questionnaire items (presented with drop down boxes). each question has a right answer (1), a wrong answer (0) and the option of refused (99). i want to calculate the total trials completed, in other words, count up how many trials the individual completed with either a 1 or a 0, excluding any 99's (which indicates a refusal). any suggestions would be greatly appreciated.

thanks,
angela

 

[jwindon]

This code works similar to what you want to do. Its choppy and is LIKE what you want to do. Still, the code does work.

Sorry, I don't have any time to explain this all, but basically, use DCOUNT to get the totals of all questions with the answer = 1; DCOUNT to get all the totals where the answer =0; and the same for REFUSED (99). Then use a formula to get the results you want.

Function naValue(ScoreValue As Integer)

Dim ScoreTotal As Integer
ScoreTotal = ScoreValue

Const NoOfQuestions = 7

Dim X As Integer
Dim Y As Integer
Dim z As Integer
Dim xx As Integer
Dim yy As Integer
Dim zz As Integer
Dim uu As Integer

X = DCount("[Score1]", "tblEvaluationData", "[Score1]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
Y = DCount("[Score2]", "tblEvaluationData", "[Score2]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
z = DCount("[Score3]", "tblEvaluationData", "[Score3]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
xx = DCount("[Score4]", "tblEvaluationData", "[Score4]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
yy = DCount("[Score5]", "tblEvaluationData", "[Score5]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
zz = DCount("[Score6]", "tblEvaluationData", "[Score6]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
uu = DCount("[Score7]", "tblEvaluationData", "[Score7]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")

MsgBox ("Counts are " & X & " " & Y & " " & z & " " & " " & xx & " " & yy & " " & zz & " " & uu)

Dim tx As Integer
tx = X + Y + z + xx + yy + zz + uu

Dim ScoreDivider As Integer
ScoreDivider = NoOfQuestions - tx

MsgBox ("Score divider is " & ScoreDivider)

Dim OveralScore As Integer
OveralScore = ScoreValue / ScoreDivider

MsgBox "The score for this record is " & OveralScore


End Function