Using Report Filter Property

[CanadianAccessUser]

Hi,

I'm trying to use the filter in the properties section on my report instead of multiple queries in an attempt to spead my database up.
I can reference the query I'm using no problem so most of my reports are working fine.

The problem:
One report in particular contains sums and averages which are causing another query level.
1. tblTelephony - Contains date specific info
2. qryTelephony - Contains date specific info
3. qryScorecard - Contains Sums and averages of qryTelephony
In my filter I need to reference the dates in tblTelephony or qryTelephony in order to filter out the dates outside of my date range but the filter can't find either of them.

The question:
Am I limited to the information in qryScorecard? or should I be able to reference the table or subquery?

Thanks,
Canadian

 

[vbaInet]

1. tblTelephony - Contains date specific info
2. qryTelephony - Contains date specific info

In my filter I need to reference the dates in tblTelephony or qryTelephony in order to filter out the dates outside of my date range but the filter can't find either of them.

Canadian, it's not completely clear whether qryTelephony is simply based on tblTelephony and pulling all (or specific fields). I'm unsure what you mean by the second statement. Perhaps you can elaborate? If you mean entering criteria, can't you do that directly in the query?

 

[CanadianAccessUser]

qryTelephony is basically a copy of the table with minor criteria in the query.
I can't put the date range in the query as I will be using this for multiple date ranges on separate reports and I'm trying to limit my query use in order to avoid the db getting bogged down.

tblTelephony has all info
qryTelephony has all info with some criteria eg. [calls]<>0

My criteria for the report itself is:

[B]((([tblTelephony].[TelephonyDate] Between [Forms]![NavPage]![StartDate] And [Forms]![NavPage]![EndDate])))[/B] And
(((qryTelephony.Agent=IIf(IsNull([Forms]![NavPage]![Agent]),[qryTelephony]![Agent],[Forms]![NavPage]![Agent])))) And
(((qryTelephony.Coach=IIf(IsNull([Forms]![NavPage]![Coach]),[qryTelephony]![Coach],[Forms]![NavPage]![Coach]))))

The theory behind the code works in other reports but for some reason this one can't find qryTelephony.

 

[CanadianAccessUser]

I'm doing research on subqueries... this may be the answer.

 

[vbaInet]

... and I'm trying to limit my query use in order to avoid the db getting bogged down.

You should be using queries more often. They're there to speed things up not slow things down.

Let's see the full SQL query.

Also does the form remain open when you run the report?

 

[CanadianAccessUser]

SELECT qryActiveAgents.Coach, qryActiveAgents.Agent, qryTotalCalls.SumOfCalls,
qryTotalCalls.SumOfTransferredCalls, qryTotalCalls.AvgOfAverageHandlingTime, qryTotalCalls.AvgOfTalkTime,
qryTotalCalls.AvgOfAfterCallWork, qryTotalCalls.AvgOfHold_Time, qryTotalCalls.AvgOfBreakAux,
qryTotalSales.SumOfPSUs, qryTotalLeads.SumOfLeads, qryTotalQuality.SumOfCountOfQualityID,
qryTotalQuality.AvgOfQualityTotal, qryTotalQuality.AvgOfRecommendR, qryTotalNPS.SumOfCountOfSurveyID, 
qryTotalNPS.AvgOfNPS, qryTotalNPS.AvgOfScore, qryTotalNPS.AvgOfCSAT, qryTotalFCR.AvgOfRepeatCalls
 
FROM ((((((qryActiveAgents LEFT JOIN qryTotalCalls ON qryActiveAgents.Agent = qryTotalCalls.Agent) 
LEFT JOIN qryTotalSales ON qryActiveAgents.Agent = qryTotalSales.Agent) LEFT JOIN qryTotalLeads 
ON qryActiveAgents.Agent = qryTotalLeads.Agent) LEFT JOIN qryTotalFCR ON 
qryActiveAgents.Agent = qryTotalFCR.Agent) LEFT JOIN qryTotalQuality ON 
qryActiveAgents.Agent = qryTotalQuality.Agent) LEFT JOIN qryTotalNPS ON 
qryActiveAgents.Agent = qryTotalNPS.Agent) LEFT JOIN qryTotalKudos ON 
qryActiveAgents.Agent = qryTotalKudos.Agent
 
GROUP BY qryActiveAgents.Coach, qryActiveAgents.Agent, qryTotalCalls.SumOfCalls, 
qryTotalCalls.SumOfTransferredCalls, qryTotalCalls.AvgOfAverageHandlingTime, qryTotalCalls.AvgOfTalkTime, 
qryTotalCalls.AvgOfAfterCallWork, qryTotalCalls.AvgOfHold_Time, qryTotalCalls.AvgOfBreakAux, 
qryTotalSales.SumOfPSUs, qryTotalLeads.SumOfLeads, qryTotalQuality.SumOfCountOfQualityID, 
qryTotalQuality.AvgOfQualityTotal, qryTotalQuality.AvgOfRecommendR, qryTotalNPS.SumOfCountOfSurveyID, 
qryTotalNPS.AvgOfNPS, qryTotalNPS.AvgOfScore, qryTotalNPS.AvgOfCSAT, qryTotalFCR.AvgOfRepeatCalls
 
HAVING (((qryTotalCalls.SumOfCalls)<>0));

Plus I want the following filtered in the report:

((([tblTelephony].[TelephonyDate] Between [Forms]![NavPage]![StartDate] And [Forms]![NavPage]![EndDate]))) And
(((qryTelephony.Agent=IIf(IsNull([Forms]![NavPage]![Agent]),[qryTelephony]![Agent],[Forms]![NavPage]![Agent])))) And
(((qryTelephony.Coach=IIf(IsNull([Forms]![NavPage]![Coach]),[qryTelephony]![Coach],[Forms]![NavPage]![Coach]))))

The form remains open as it is my navigation page and the report opens as a subreport in the navigation form.

I am self taught in VBA, practically self taught in MS Access, and my naming convention sucks so bare with me.

 

[vbaInet]

Plus I want the following filtered in the report:

((([tblTelephony].[TelephonyDate] Between [Forms]![NavPage]![StartDate] And [Forms]![NavPage]![EndDate]))) And
(((qryTelephony.Agent=IIf(IsNull([Forms]![NavPage]![Agent]),[qryTelephony]![Agent],[Forms]![NavPage]![Agent])))) And
(((qryTelephony.Coach=IIf(IsNull([Forms]![NavPage]![Coach]),[qryTelephony]![Coach],[Forms]![NavPage]![Coach]))))

Well the code you wrote obviously isn't working so I can't tell what you want to do from code that doesn't work. Tell me in bullet points what it should do and we can take it from there.

 

[CanadianAccessUser]

Separately, the codes work beautifully.
The filter fails because the report properties filter setting can't see tblTelephony or qryTelephony. How do I get the filter property in the report to find tblTelephony when there are multiple query levels between the table and the report?
I need this filtered at the report level if at all possible. If it's not possible I need to move on and do it another way.

 

[vbaInet]

What I'm saying is I don't know what your criteria is supposed to do.

 

[CanadianAccessUser]

OH! haha ok

The first line: ((([tblTelephony].[TelephonyDate] Between [Forms]![NavPage]![StartDate] And [Forms]![NavPage]![EndDate])))
[tblTelephony].[TelephonyDate] is the date of the info I'm working with (needs to be filtered using a date range so that I can pull one month/week/day of statistics.)
[Forms]![NavPage]![StartDate] is a combo box on my nav page for the start of the date range
[Forms]![NavPage]![EndDate] is the same for the date range end date

The second line: (((qryTelephony.Agent=IIf(IsNull([Forms]![NavPage]![Agent]),[qryTelephony]![Agent],[Forms]![NavPage]![Agent]))))
qryTelephony.Agent is the name of the agent the stats belong to in qryTelephony
[Forms]![NavPage]![Agent] is a combo box on my nav page for the Agent name so the supervisor can pull stats for one agent only (if combo box is null use all agents in the qry)

The third line: (((qryTelephony.Coach=IIf(IsNull([Forms]![NavPage]![Coach]),[qryTelephony]![Coach],[Forms]![NavPage]![Coach]))))
qryTelephony.Coach is the name of the supervisor
[Forms]![NavPage]![Coach] is a combo box on my nav page for the Supervisor name so the supervisor can pull stats for all agents on their team (if combo box is null use all coaches in the qry showing all agents)

Clear as mud?

 

[vbaInet]

Basically you can't filter by fields that don't exist in your query. Your report does not include the query so I don't see how you would hope to filter by something that isn't there.

If your query is becoming too long and complex, create a query based on that one and link it to qryTelephony. Then use the newly built query as the record source of your report.

 

[CanadianAccessUser]

Thank you!
Now I know how to move forward.

Your help is appreciated!

 

[vbaInet]

No problemo!

 

[CanadianAccessUser]

Hmmmmmm
Now that I'm doing this with multiple queries it's telling me that I cannot open any more databases... this is why I soooo wanted to filter that in the report... I'll keep working at it tho.

 

[vbaInet]

Can you expand on the problem?