ChrisCalvert
Technical User
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.
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.