By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 444,124 Members | 1,746 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

# Need calulations to work with # of fields in a Report

 P: n/a 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 Nov 12 '05 #1
Share this Question
3 Replies

 P: n/a 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" 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 Nov 12 '05 #2

 P: n/a 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! Nov 12 '05 #3

 P: n/a 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! Nov 12 '05 #4

### This discussion thread is closed

Replies have been disabled for this discussion.