Need calulations to work with # of fields in a Report

[CSDunn]

Hello,
I have a situation in which each record on a report has 40 fields, any
one of which can contain a value of 'C', or 'I', or 'B'. I need to
create one calculated field on the report that will COUNT the number
of fields with a value of 'C'(and shows the integer result), and
another calculation that will take the COUNT of the fields with a
value of 'C' and divide that by the total number of fields. The second
calculation would be expressed as a percentage, allowing for two
decimal places.

The field names for the forty fields are Q1, Q2, Q3.....Q40. A sample
of the records would look like this:

StudentID Name Q1 Q2 Q3 Q4 #Correct %Correct
1234 John C C I B 2 50.00%

How would I accomplish this, and what event would the procedures be
assigned to in the report? The records in the report that requires the
calculations is in the detail section of the report, and the report
itself is a sub report located within the detail section of the main
report.

Thanks for your help!

CSDunn

[DFS]

You need to write a function. If your report controls are labeled Q1, Q2,
.... it will be much easier. If not, you'll have to type each report control
name in the code.

This code goes in the Detail_Format event of the subreport containing the
Question answers:
Public Sub Detail_Format()

'GET COUNTS
for i = 1 to 40
if me("Q" & i) = "C" then
countC = countC + 1
elseif me("Q" & i) = "I" then
countI = countI + 1
elseif me("Q" & i) = "B" then
countB = countB + 1
endif
next i

'SHOW C COUNT
Me.txtCountC = countC

'SHOW C PERCENT (FORMAT THE FIELD ON THE REPORT)
Me.txtPercentC = countC / 40

End Sub
As you can see, if your fields aren't named with an increasing number,
you'll have a big headache.


"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

Hello,
I have a situation in which each record on a report has 40 fields, any
one of which can contain a value of 'C', or 'I', or 'B'. I need to
create one calculated field on the report that will COUNT the number
of fields with a value of 'C'(and shows the integer result), and
another calculation that will take the COUNT of the fields with a
value of 'C' and divide that by the total number of fields. The second
calculation would be expressed as a percentage, allowing for two
decimal places.

The field names for the forty fields are Q1, Q2, Q3.....Q40. A sample
of the records would look like this:

StudentID Name Q1 Q2 Q3 Q4 #Correct %Correct
1234 John C C I B 2 50.00%

How would I accomplish this, and what event would the procedures be
assigned to in the report? The records in the report that requires the
calculations is in the detail section of the report, and the report
itself is a sub report located within the detail section of the main
report.

Thanks for your help!

CSDunn

[Chris Dunn]

Thanks for your help. I failed to mention that some of the fields will
be NULL, and my percentage calculation will not be correct if I always
use 40 as a denominator.

How would I reconfigure the following code to account for "Q" fields
that are NULL, and exclude them from at least my percentage calculation?

'GET COUNTS
For i = 1 To 40
If Me("Q" & i) = "C" Then
countC = countC + 1
ElseIf Me("Q" & i) <> "C" Then
countC = countC + 0
End If
Next i

'SHOW C COUNT
Me.txtCountC = countC

'SHOW C PERCENT (FORMAT THE FIELD ON THE REPORT)
Me.txtPercentC = 100 * (countC / 40)
End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Chris Dunn]

I think I have what I needed now:

'GET COUNTS
For i = 1 To 40
If Me("Q" & i) = "C" Then
CountC = CountC + 1
ElseIf Me("Q" & i) = "I" Then
CountI = CountI + 1
ElseIf Me("Q" & i) = "B" Then
CountB = CountB + 1
End If
Next i

'SHOW C COUNT
Me.txtCountC = CountC

'SHOW C PERCENT (FORMAT THE FIELD ON THE REPORT)
Me.txtPercentC = 100 * (CountC / (CountC + CountI + CountB))

I haven't tested this in the case where one of the values might but
NULL, but I don't see why it would not work.

Thanks again for your help!
CSDunn

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!