Average of recordset fields?

[sherlocked]

Hello Experts,

I'm attempting to get the average of a field in a recordset that groups results by year. Code is below. I am getting a "the field is not large enough for the data you attempted to paste" error and can't figure out why. Any ideas on what I'm doing wrong?

Public Function GetAgencyHistory()

Dim AgencyHis1 as Double
Dim ThisYear As Integer

Dim rst As Recordset
Dim sql As String

ThisYear = DatePart("yyyy", Date)

sql = "SELECT Count(tblRecords.ID) AS CountOfID " _
    & "FROM tblRecords " _
    & "GROUP BY tblRecords.ExecYear, tblRecords.ExecMonth, tblRecords.FraudReason, tblRecords.Agency " _
    & "HAVING tblRecords.[ExecMonth] = " & Forms!frmMain.cmboMonth & " AND " _
    & "tblRecords.[ExecYear] <> " & ThisYear & " AND " _
    & "tblRecords.[FraudReason]= 'Impostor - Living ID (Suspect Alien)' AND " _
    & "tblRecords.[Agency] = '" & Forms!frmMain.cmboAgency & "' "
    
With CurrentDb.OpenRecordset(sql)
   If Not .EOF Then
   AgencyHis1 = DAvg("CountOfID", sql)
   .Close
   End If
End With

End Function

 

[The_Doc_Man]

What, in ENGLISH, are you trying to determine? Your With statement might open the recordset but its values aren't necessarily available in the way you set it up.

Does it highlight a particular line of code? I'm betting it didn't like the DAvg line, but I could be wrong. And I think part of it is that domain aggregates only work on tables or stored queries, not on SQL ad-hoc/dynamic queries. So the "sql" argument of DAvg exceeds the allowed name size for the domain argument (2nd argument).

Supplemental:

https://support.office.com/en-us/article/DAvg-Function-c7270c5d-e3b9-4f26-9615-2133a277066d

From that article, regarding the domain argument:

domain

Required. A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.

There is no provision for this to be a dynamic SQL string.

 

[sherlocked]

I'll do my best to explain.

My query is counting up the total number of records in each year and this value is in the "CountOfID" field of my query result. For example, there might be three records in the recordset, one for each year. The total number of records might be 32, 26 and 14.

I am trying to display on my report the average of the results, which in this case would be 24. My users are interested in seeing both current totals and historical averages on the same report, so I can't use just one query to populate the data.

I hope this clarifies.

 

[sherlocked]

As an edit, to add:

I see what you're saying about the "sql" domain being too large. It is indeed the "DAvg" line that is throwing the error.

That being said, do you have any thoughts about how I can accomplish what I'm trying to do?

 

[plog]

You could do this all by SQL if you make your existing query a subquery to one that actually determines the average. Something like:

sql = "SELECT AVG(CountOfID) AS TheAverage FROM (" & sql & ")"

I'm pretty sure I'm missing an alias or a parenthesis somewhere, but that's the jist.

 

[sherlocked]

This worked elegantly and perfectly. Classic case of knowing what I wanted to do but not having the experience to know WHAT to do. Thank you so very kindly!