Reports and Charts 1

[kimmer7389]

I have a report that is sorted by Unit and then Date by Month. The report is displaying all of the records correctly. The users can select a Unit and enter a range of dates from a form to filter the report. The filter is working on the report data.

Here is the problem...
I would like to have a line chart that displays the data per Unit and then by the date range. Bascially the chart should only display the data that is on the report. The Unit, the months for the date range, and the percentage.

I have the line chart on the report. The chart is in the Unit footer on the report so it is displaying the correct Unit. However, no matter what I do I cannot get the chart to display the correct date range. It is currently displaying all of the dates by month that have ever been entered into the database.

The row source for the chart is:
SELECT [Copy Of qry_HandHygienebyMonthandUnit].Date, Sum([Copy Of qry_HandHygienebyMonthandUnit].Percentage) AS SumOfPercentage
FROM [Copy Of qry_HandHygienebyMonthandUnit]
GROUP BY [Copy Of qry_HandHygienebyMonthandUnit].Date, [Copy Of qry_HandHygienebyMonthandUnit].Unit, (Year([Date])*12+Month([Date])-1);

It appears that the filter that is set from the form is ignored by the chart.

 

[dhookom]

I would expect to see a WHERE clause in the Row Source like:

SELECT [Copy Of qry_HandHygienebyMonthandUnit].Date, Sum([Copy Of qry_HandHygienebyMonthandUnit].Percentage) AS SumOfPercentage
FROM [Copy Of qry_HandHygienebyMonthandUnit]
[red]WHERE [Date] Between Forms!frmDate!txtStart and Forms!frmDate!txtEnd[/red]
GROUP BY [Copy Of qry_HandHygienebyMonthandUnit].Date, [Copy Of qry_HandHygienebyMonthandUnit].Unit, (Year([Date])*12+Month([Date])-1);

Duane
Hook'D on Access
MS Access MVP

 

[kimmer7389]

Ah!

Ok so I added the Where clause...

SELECT [Copy Of qry_HandHygienebyMonthandUnit].Date, Sum([Copy Of qry_HandHygienebyMonthandUnit].Percentage) AS SumOfPercentage
FROM [Copy Of qry_HandHygienebyMonthandUnit]
WHERE ((([Copy Of qry_HandHygienebyMonthandUnit].Date) Between [Forms]![frm_menu]![Start] And [Forms]![frm_menu]![End]))
GROUP BY [Copy Of qry_HandHygienebyMonthandUnit].Date, [Copy Of qry_HandHygienebyMonthandUnit].Unit, (Year([Date])*12+Month([Date])-1);

But now my query return 0 records.

 

[kimmer7389]

It helps if I put the correct names in the query! It works now! Thank you for your help!

 

[kimmer7389]

Now how do I change the X axis to only show the months for the date range entered?

The data is displaying the correct data points but my x axis is showing all the months and years ever entered into the database.

 

[dhookom]

Are you suggesting the date range isn't limited to the values entered into the controls on the form? Is your date format m/d/y?

Duane
Hook'D on Access
MS Access MVP