Summary Functions taking too long and Dynamic Chang of Detail Field Backgrounds

  • Thread starter Thread starter Derrick
  • Start date Start date
D

Derrick

Any ideas?

I have a comprehensive report that needs to either total
or calculate averages for each colum in the report. For
the averages they do not want averages to include that of
totals. Therefore I am using the DAvg function.

In doing so, my report is now running 5-10 minutes longer
just because of the DAVG conditions. And it now will not
print the total page.

I then re-wrote the stored procedure to generate the
totals and averages. It now takes the report to run in a
few seconds. The problem now I am having is the darkening
of fields in the total line and also darkening fields in
the averages as each column has only Summary totals or
averages.

I.e. 2 Summary lines, one for totals and one for averages.
Columns in the Total lines that have no values are to be
blackened out and conversly done for the averages line.

TIA
 
Note:
When I tried to format the cell by coding the following
the whole column would blank out and not only for that
specific line

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Sum" Then
Dim txt As TextBox
Set txt = ctl
If txt.text = "" Then
ctl.BackColor = RGB(255, 255, 255)
End If
End If
Next ctl
End Sub
 
You haven't explained why you can't just use totals in your report with
expressions like:
=Sum(MyField)
or
=Avg(MyField)
DSum() and DAvg() are rarely needed in any report.

If you have code to set a format, your code should also set it back by using
an "else" line.
 
Back
Top