Capture record count in detail section of report

[Wiz47]

I need to average a set of values in a report based on the number of records displayed. That number will change depending on the report parameters. I plan to use a statement like the following:

Textbox Name "TotalCount"

=sum([myfield]) / Sum([RecordCount])

Then reference that in the footer with

=TotalCount

The issue I'm having is that I'm not able to capture the record count as it occurs. I normally count records using the following convention.

"RecordCount" (name of the unbound textbox)

=1

(in detail section as a hidden textbox)

Then

= RecordCount

in the footer.

Naturally, this doesn't work when I reference it in the first code for the average calculation as it asks me for the RecordCount number.

 

[Galaxiom]

Put this in the footer. The other steps are unnecessary.

=Sum([myfield]) / Count(*)

Note that references summed or counted in the footer are to fields in the recordset, not controls on the form.

 

[Wiz47]

Put this in the footer. The other steps are unnecessary.

=Sum([myfield]) / Count(*)

Note that references summed or counted in the footer are to fields in the recordset, not controls on the form.

I'm still getting an

#error

when it runs:

=Sum([AnswerTotal]) / Count(*)

AnswerTotal is a long integer.

 

[arnelgp]

remove the control source of your average control. on your report's load event:

Private sub Report_load()
Dim lngAnswerTotal As Long
Dim lngCount As Long
Dim rs As Dao.Recordset
Set rs = Me.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .Eof
lngAnswerTotal = lntAsnwerTotal + NZ(!FieldToTotal, 0)
lngCcount = lngCount + 1
.MoveNext
Wend
rs.Close
set rs = Nothing
Me.yourDisplayControl = CDbl(Format(Cdbl(lngAnswerTotal / lngCount),"0.00"))
End Sub

replace the blue-lettered ones with correct field/control name.

 

[Wiz47]

remove the control source of your average control. on your report's load event:

Private sub Report_load()
Dim lngAnswerTotal As Long
Dim lngCount As Long
Dim rs As Dao.Recordset
Set rs = Me.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .Eof
lngAnswerTotal = lntAsnwerTotal + NZ(!FieldToTotal, 0)
lngCcount = lngCount + 1
.MoveNext
Wend
rs.Close
set rs = Nothing
Me.yourDisplayControl = CDbl(Format(Cdbl(lngAnswerTotal / lngCount),"0.00"))
End Sub

replace the blue-lettered ones with correct field/control name.

Private sub Report_load()
Dim lngAnswerTotal As Long
Dim lngCount As Long
Dim rs As Dao.Recordset
Set rs = Me.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .Eof
 lngAnswerTotal = lntAsnwerTotal + NZ(![COLOR=Blue][/COLOR][COLOR=Blue]AnswerTotal[/COLOR], 0)
lngCcount = lngCount + 1
.MoveNext
Wend
rs.Close
set rs = Nothing
Me.[COLOR=Blue]TxtAnswerTotal2[/COLOR] = CDbl(Format(Cdbl(lngAnswerTotal / lngCount),"0.00"))
End Sub

I replaced the variables in blue, where AnswerTotal is the value to calculate (Long integer) and TxtAnswerTotal2 is the unbound textbox control in the footer. But, I get this error from the "On Load" event ...

"Ambiguous Name Detected"

I've replaced the name of the value control with the *actual* field name, but it doesn't change anything.

 

[Wiz47]

remove the control source of your average control. on your report's load event:

Private sub Report_load()
Dim lngAnswerTotal As Long
Dim lngCount As Long
Dim rs As Dao.Recordset
Set rs = Me.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .Eof
lngAnswerTotal = lntAsnwerTotal + NZ(!FieldToTotal, 0)
lngCcount = lngCount + 1
.MoveNext
Wend
rs.Close
set rs = Nothing
Me.yourDisplayControl = CDbl(Format(Cdbl(lngAnswerTotal / lngCount),"0.00"))
End Sub

replace the blue-lettered ones with correct field/control name.

Okay, I worked through the other issue. It was a word mis-spelled in the sub (lntAsnwerTotal). Now I get the error:

"Method or data member not found (RecordSetClone)." Do I need to update my library reference? The good news, I think we're getting closer to a solution here.

 

[Galaxiom]

I'm still getting an

#error

when it runs:

=Sum([AnswerTotal]) / Count(*)

AnswerTotal is a long integer.

This really should be working if AnswerTotal is a field in whatever you have as the RecordSource of the form.

 

[Wiz47]

This really should be working if AnswerTotal is a field in whatever you have as the RecordSource of the form.

AnswerTotal is a field (Long Integer) in the recordsource for the report. I don't understand why it's not working either. I might create a new Db, then copy the forms, tables, and fields over to see if that's the issue.

 

[Wiz47]

This really should be working if AnswerTotal is a field in whatever you have as the RecordSource of the form.

Update: It *is* working now. Thanks for the assistance.

 

[Wiz47]

This is what it was used for. A Db that tracked daily food intake and produced a report for the doctor. My friend thanks you, and I do too.