Filtering a Chart Report

[infomania]

I have a series of chart reports that need to be filtered by 1 or more field criteria.

The initial query that produced a nice line chart (#Trucks arriving at hour intervals) for ALL data is:

SELECT qryMillArrivalTime.HOUR
, Sum(qryMillArrivalTime.CountOfTRACKING_DATA_ID) AS NBR_TRUCKS
FROM qryMillArrivalTime
GROUP BY qryMillArrivalTime.HOUR;

I modified the query to include an additional field:
SELECT qryMillArrivalTime.HOUR
, Sum(qryMillArrivalTime.CountOfTRACKING_DATA_ID) AS NBR_TRUCKS
, qryMillArrivalTime.MILL
FROM qryMillArrivalTime
GROUP BY qryMillArrivalTime.HOUR,
, qryMillArrivalTime.MILL;

The code to run the reports looks like this:

'stWhereClause set dynamically from a combo box selection
'on the form and passed into this code

stDocName = [Forms]![fmReportSelection]![fmReportSelectionSubform]![REPORT_NAME]
'MsgBox stWhereClause
DoCmd.OpenReport stDocName, acPreview, , stWhereClause

When the report is run, even though the stWhereClause is valid (ex. MsgBox: MILL='TEXARKANA'), the report is not filtered at all.

This filtering method works great on standard reports but the charts don't accept the where criteria.

Any ideas?

Thanks and Happy Holidays.

Infomania

 

[GingerR]

the where clause will filter the report for the mill, not the chart, cause you're opening the report with the where clause.

in your report design, you can try making child/master links of the chart = Mill. i've had trouble with that before, but if it works for you, great.

sometimes I put the criteria right into the query that the chart is based on (i.e. [Forms]![FormName]![cboMill])

try one of those things and let us know how it goes.

 

[Dave177]

Hi there. I have a different but very similar problem.
Would it be true to say that you can't filter information in a query for charts using a list box?
(I don't understand the Child/master links property of the chart at all - I've tried looking on the web but haven't had much luck!)

 

[GingerR]

You could use a list box. If it's multi-select, you have to write the choices from the list box to a temp table. But otherwise I'd just reference it in the recordsource of the chart.

i've never used Master/Child links for a chart, and I've build i'd say at least hundreds of them.

what's your issue? if you need more help give more details.


thanks--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dave177]

I would like to be able to illustrate course questionnaire results as a graph but able to use a list box to decide which course(s) should be shown on the graph.

The table containing the results is [tblCourseAssessments] which contains the following fields:
[Course_instance] which is a code denoting a specific course
[Handouts] a score for handouts
[Visual_aids] a score given for visual aids. etc.
A query [qryCourseAssessments] is then used to link [Course_instance] to [venue_name] and [start_date] of the [instances] table and an expression [Date & Location] is made combining these two fields. This will then appear on the x axis.
The graph in the report [rptCourseAssessmentChart] uses the following query:

SELECT qryCourseAssessments.[Date & Location], Avg(qryCourseAssessments.[Overall_objectives_met?]) AS [Overall Objectives Met?], Avg(qryCourseAssessments.Visual_aids) AS [Visual Aids], Avg(qryCourseAssessments.Handouts) AS [Course Handouts], GROUP BY qryCourseAssessments.[Date & Location], qryCourseAssessments.start_date ORDER BY qryCourseAssessments.start_date;

So therefore a listbox on form [frmCourseAssessmentAnalysis] should be able to select several specific instances and output a chart accordingly.

I have been having real problems with this and have been told that when muliple select property of a list box is used "it's property is changed to Null meaning that nothing will appear in the graph". Any help would be really appreciated.

Also 6 course instances go together on one page well but a seventh makes a squash. How can I tell it to start a new page on the 7th?

regards
Dave

 

[GingerR]

So let me get this right: someone's going to pick possibly multiple things in a list box, hit a button, and see a report which has one chart for each thing chosen in the list box?

IF that's true, you have to first look up how to handle multi-select list boxes.You'll end up with a table which lists say, 5 course numbers that have been selected from the list box.

Then the report record source will be that table. Put a text box in the detail section of the report for the Course Number. Then put the chart in the detail section of the report. In the query that the chart is based on, put in the criteria under Course Number of that text box, i.e.

[Reports]![ReportName]![txtCourseNumber]

i don't understand your last sentence, sorry. you have to mess with margins and your detail height to make things how you want them.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dave177]

Hi,
I've done everything you said and also put a text box next to the list box but when the multi-select option is on simple or extended nothing appears in the text box that shows the items selected in the list box.
Is some code needed - or am I doing something wrong?

Dave

 

[GingerR]

As I noted above, you need to look up how to handle multi-select list boxes. There's code to write (look in any text book or HELP or this forum). You end up with a temp table which holds the ID's of the items selected in the list box. it's THIS table that you use in your report.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dave177]

Ginger,
Thanks alot for your help - it now works.
Sorry I was so slow on the uptake of this - you can probably tell I'm quite new to all of this.
D

 

[GingerR]

No problem. There's always a lot to learn for me too!!

Glad it worked out for you.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244