DAvg with user restriction in report.

[ChrisCalvert]

Hello:

I have a report I am running that calculates the quality scores of phone reps. This is a monthly report, and I use a query to get the report records based on one field. This query just pulls records from a few tables based on the [Probation] Field. (If the person is on probation, they are not figured into the scores.) When the report button is clicked on, I use the following code to determine for what month to run the report.


Dim stDocName As String
Dim strWhere, strSelectedMonth, strErMsg As String
strSelectedMonth = InputBox("Enter the number of the month for this report.", "Select Month&quot
strWhere = "((Month([Date of Call])=(" & strSelectedMonth & &quot))"
stDocName = "rptMonthlyQAReport"
DoCmd.OpenReport stDocName, acPreview, , strWhere
Exit Sub


However, in the report, I need to calculate averages based on the Area Supervisors, and I cannot make the normal grouping do this. I my DAvg Fields like so:

=DAvg("[Final Score]","qryJpAvgScore&quot


The qryJPAvgScore is a query that get the non-probation scores for a certain supervisor. Now, the only problem I have is I need to restrict this to the same date as the rest of the report. I don't know whether to do this in the query the Davg is based on, or in the DAvg call in the report... or what. I tried to set the value of an unbound text box on the report and use VBA to retrieve the value for the month.. but that says I cannot set the value of the text box. Any help/suggestions appreciated.

 

[MichaelRed]

There are more ways to skin this cat than it has lives. But, I would advise against DAvg (or any / all of the 'Domain Aggregate' functions. They are just simplistic (and somewhat crippled) wrappers for an SQL query anyway, so rather tha the lazy way, just write a seperate query which can be as cmplex as necesssary and utilize values whereever it is convenient and useful (a-la your UNBOUND text box).


MichaelRed
m.red@att.net

Searching for employment in all the wrong places