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

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

If you want to show totals on your form, add a Footer section and three controls, one for each value that you want to count. Then in each control:

RightAnswer:
=Sum(IIf(Answer = 1,1,0))

WrongAnswer:
=Sum(IIf(Answer = 0,1,0))

Refusal:
=Sum(IIf(Answer = 99,1,0))

If you want to count the answers for the whole table, use a query:

Select Answer, Count(*) As Count
From YourTable
Group By Answer;