MS Access Passing a Filter to a Graph using VBA

[RogerThisToo]

I use this code to build a filter to apply to the data (qry) in a report and it works great:

strCriteria = strCriteria & " AND [Standard] = " & "'" & cboStandard & "'"
DoCmd.OpenReport stDocName, acPreview, , strCriteria

.. the report includes a Graph with the following RowSource

SELECT qryAuditHistoryList.Year, 
   Sum(qryAuditHistoryList.[NCR-Count]) 
      AS [Total NCR], 
   Sum(qryAuditHistoryList.[OFI-Count]) 
      AS [Total OFI], 
   (Sum(qryAuditHistoryList.[OFI-Count])+
      sum(qryAuditHistoryList.[NCR-Count]))/
         Count(qryAuditHistoryList.AuditNum) 
      AS Average FROM qryAuditHistoryList 
GROUP BY qryAuditHistoryList.Year;

The report gets is data from "qryAuditHistor yList" and that is filtered, the graph is not. So I get a filtered data table, but the Graph always contains the entire dataset.

How can I filter the graph.. please and thank you. (I am not an experienced programmer.. so code would be appreciated)

[zmbd]

You need to change the record source of your graph.
Open the report in design mode.
Right click on the graph.
Select properties in the context menu
Select the data tab
Change the row source to the filtered query.

Please note, that we do not normally provide completed code or projects. > FAQ

[RogerThisToo]

Thanks for the fast reply... ok about the code.

The query is filtered dynamically just before the report is called. I do refere to it, but the filter is not applied. Do I need to create a new query or even table, from the dynamic filtered query, then refer to that new query/table in rowsource?

[zmbd]

Here's the best options I have for you at this point:

VBA: In the on open event pull the filter criteria from the report (me.filter) and then build the SQL for the chart row source and set to this sql.

Query: Create a query that refers to the control on your chart that set the filter. With this, you can set the report to use this query too instead of passing the filter thru as you are doing now.

I do not know of a way for the Chart to use the report's filter (v2003 thru 2010).