Hello,
I have been trying to find a best way to open a report based on form filtered data, with additional chart on it. The form and report works perfectly fine. Then I created second query and added a chart based on this query to the existing report. It does work well but unfortunately I don't know how to pass filter from form to this second query.
first query is just basic query with data from table. The second query is based on first one and has calculation fields which are row source for my chart.
In the main form user can just open report with all data, or can apply filter by selecting values in combo boxes, this then passes strWhere string to report.
Second query:
report with chart only:
report with data and chart
Once again, I don't know how to pass strWhere from main form to report with chart as chart has separate query.
I tried
- but this gives me an error
Can anyone advice please?
I have been trying to find a best way to open a report based on form filtered data, with additional chart on it. The form and report works perfectly fine. Then I created second query and added a chart based on this query to the existing report. It does work well but unfortunately I don't know how to pass filter from form to this second query.
first query is just basic query with data from table. The second query is based on first one and has calculation fields which are row source for my chart.
In the main form user can just open report with all data, or can apply filter by selecting values in combo boxes, this then passes strWhere string to report.
Second query:
Code:
SELECT Q_ActionPlanner.userName AS Owner, Sum(IIf(tRunDown="D",tExecuteTime,0)) AS ExecTimeD, Sum(IIf(tRunDown="R",tExecuteTime,0)) AS ExecTimeR
FROM Q_ActionPlanner
GROUP BY Q_ActionPlanner.userName
ORDER BY Q_ActionPlanner.userName;
report with chart only:
Code:
Private Sub cboAPeffort_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
If strWhere <> "" Then
DoCmd.OpenReport "R_APeffort", acViewReport, , strWhere & " AND tTaskDueDate <= Date()"
Else
DoCmd.OpenReport "R_APeffort", acViewReport, , "tTaskDueDate <= Date()"
End If
End Sub
report with data and chart
Code:
Private Sub cboAPprintarea_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
If cboAPduedate = "Today" Then
If strWhere <> "" Then
DoCmd.OpenReport "R_APdailyplan2", acViewReport, , strWhere & " AND tTaskDueDate <= date()"
Else
DoCmd.OpenReport "R_APdailyplan2", acViewReport, , "tTaskDueDate <= Date()"
End If
Else
MsgBox ("Please Select Day Before Printing.")
End If
End Sub
Once again, I don't know how to pass strWhere from main form to report with chart as chart has separate query.
I tried
Code:
DoCmd.OpenQuery "Q_APchart", acViewPivotChart, acReadOnly ', strWhere & " AND tTaskDueDate <= Date()"
Can anyone advice please?