Help in calculate average

[Silvio]

Hello, I have a report with control5 in the report header that counts the
total records in the entire report. This report is grouped by user, the User
footer also had a control8 that count the number of groups (people) by having
the following properties: Control Source = 1 and Running Sum = Over All.
However, when I try to divide: =[control5]/[control8] it does not work, the
report keep asking for the value of control8. What I am trying to do is to
divide the grad total by the number of users in the report (e.g. total record
in report = 200, total user listed = 5, so the average would be 200/5 = 40 .
Of course, the number of record and user is different every time I run the
report. What I am doing wrong?

 

[Ken Snell \(MVP\)]

In a report, you cannot use the calculated result from another control as a
"reference" in the Control Source expression of a control. You must repeat
the Control Source expression from control8 in your Control Source
expression for this control.

However, because you're using the RunningSum property of control8 as part of
getting its value, you'll probably need to use programming in the Format
event of the footer section to write a value into this new control, using
the calculation that you now have as its Control Source.

Private Sub NameOfFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.NameOfControl.Value = Me.control5.Value / Me.control8.Value
End Sub

 

[Ken Snell \(MVP\)]

I know that I'm not fully understanding the setup of your report, but I'm
guessing that the code may have been put in the Format event of the wrong
footer section? Did you use the group footer section?

In which "section" of the report is control8 located? From your description,
I am thinking that it may be in the detail section?

To assist you more fully, please provide a more detailed explanation of the
report's setup:
-- the report's recordsource query
-- the Sorting & Grouping properties
-- which controls are located in each of the report's sections, and what
their control sources are