Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DAvg with user restriction in report.

Status
Not open for further replies.

ChrisCalvert

Technical User
Mar 18, 2002
231
US
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")
strWhere = "((Month([Date of Call])=(" & strSelectedMonth & ")))"
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")


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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top