How to show [Year] parameter value on the chart header?

[awinnn]

Hi,
I'm having problem with displaying a [Year] parameter value on the chart header.
This is the Row Source of the chart,

SELECT [MONTH],Sum([SumOfAMOUNT]) AS [SumOfSumOfAMOUNT] FROM [qryVehicle] GROUP BY [MONTH];

Any idea? thanx in advance..

 

[dhookom]

I don't see a year parameter. Is this in the link master/child? Are you asking how to limit the results to a particular year? If you have the value in the report section, can you just place a text box on top of the chart control?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[awinnn]

Hi Duane,
thanx for reply,
the above code is in the Row Source of the chart..
the code is taken from this query,

SELECT Sum(tblVehicle.AMOUNT) AS SumOfAMOUNT, Format 
       ([RepairDate],'mmm yyyy') AS [MONTH]
FROM tblVehicle
WHERE (((Year([RepairDate]))=[ENTER YEAR]))
GROUP BY Format([RepairDate],'mmm yyyy');

any idea? thanx in advance..

 

[dhookom]

You can change your query to:
SELECT [Enter Year], Sum(tblVehicle.AMOUNT) AS SumOfAMOUNT, Format
([RepairDate],'mmm yyyy') AS [MONTH]
FROM tblVehicle
WHERE (((Year([RepairDate]))=[ENTER YEAR]))
GROUP BY [Enter Year], Format([RepairDate],'mmm yyyy');

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[awinnn]

Hi Duane,
Thanx for reply again..

Now my problem is to display [year] on the chart header. Let say if i want to view chart for year 2003, the header will be like this,
"Vehicle Summary For Year - 2003"

My problem is to display the 2003. I've put textbox and set the Control Source as =[YEAR], but it displayed as,
"Vehicle Summary For Year - #Name?"

any idea? thanx in advance..

 

[dhookom]

Create a text box with a name like "txtTitle" and set its control source to:
="Vehicle Summary For the Year - " & [Enter Year]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[awinnn]

Hi Duane,
thanx for reply..
it's not working..it displayed as #Name?
if i check on the query, the [Enter Year] is equal to Expr1..and i've included the Expr1 on the Row Source of the chart and changed the statement into this,

="Vehicle Summary For the Year - " & [Expr1]

but it still not working..
any idea? thanx in advance..

 

[dhookom]

Try set your query to:
SELECT [Enter Year] as TheYear, Sum(tblVehicle.AMOUNT) AS SumOfAMOUNT, Format
([RepairDate],'mmm yyyy') AS [MONTH]
FROM tblVehicle
WHERE (((Year([RepairDate]))=[ENTER YEAR]))
GROUP BY [Enter Year], Format([RepairDate],'mmm yyyy');
And then in the text box
="Vehicle Summary For the Year - " & [TheYear]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[awinnn]

Hi Duane..
thanx again for reply..

it's still not working..hmm...i've done exactly as u said..but still no luck..

any idea? thanx in advance..

 

[dhookom]

This is probably why I never use parameter queries. I would place a text box on a form and use this in place of your parameter prompt. The query would then be something like:

SELECT Sum(tblVehicle.AMOUNT) AS SumOfAMOUNT, Format
([RepairDate],'mmm yyyy') AS [MONTH]
FROM tblVehicle
WHERE (((Year([RepairDate]))=[Forms]![frmA]![txtYear]))
GROUP BY Format([RepairDate],'mmm yyyy');
="Vehicle Summary For the Year - " & [Forms]![frmA]![txtYear]

Make sure the form stays open.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.