Chart Error

  • Thread starter Thread starter SoggyCashew
  • Start date Start date
S

SoggyCashew

Hello I have a query that works when I run the query but when I make a chart
it gives me an error. MS Jet database engine doesnt reconize
'[Forms]![frmSwitchboard]![cboCalYear]' as a valid field name or expression.
the row source for my chart is:

TRANSFORM Sum([CountOfAccidentTypeName]) AS [SumOfCountOfAccidentTypeName]
SELECT [Department] FROM [test] GROUP BY [Department] PIVOT
[AccidentTypeName];

And my querys SQL is:

SELECT tblDepartment.Department, tblAccidents.AccidentTypeName,
Count(tblAccidents.AccidentTypeName) AS CountOfAccidentTypeName
FROM tblDepartment INNER JOIN tblAccidents ON tblDepartment.DepID =
tblAccidents.Department
WHERE (((Year([DateOfAccident])) Like [Forms]![frmSwitchboard]![cboCalYear]))
GROUP BY tblDepartment.Department, tblAccidents.AccidentTypeName;
 
I have found that charts cannot accept form parameters. What I do is create a
make table query from my original query and then use the created table data
for the chart.
 
First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

So your query should end up looking like the following. I also changed the
LIKE to equals. Unless you are using wild cards there is no good reason to
use like.
Parameters [Forms]![frmSwitchboard]![cboCalYear] Long;
SELECT tblDepartment.Department, tblAccidents.AccidentTypeName,
Count(tblAccidents.AccidentTypeName) AS CountOfAccidentTypeName
FROM tblDepartment INNER JOIN tblAccidents ON tblDepartment.DepID =
tblAccidents.Department
WHERE (((Year([DateOfAccident])) = [Forms]![frmSwitchboard]![cboCalYear]))
GROUP BY tblDepartment.Department, tblAccidents.AccidentTypeName;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I have found that charts cannot accept form parameters. What I do is create a
make table query from my original query and then use the created table data
for the chart.
Click to expand...

Actually they can but you must explicitly define the parameter. Try

PARAMETERS [Forms]![frmSwitchboard]![cboCalYear] Integer;
SELECT tblDepartment.Department, tblAccidents.AccidentTypeName,
Count(tblAccidents.AccidentTypeName) AS CountOfAccidentTypeName
FROM tblDepartment INNER JOIN tblAccidents ON tblDepartment.DepID =
tblAccidents.Department
WHERE (((Year([DateOfAccident])) =[Forms]![frmSwitchboard]![cboCalYear]))
GROUP BY tblDepartment.Department, tblAccidents.AccidentTypeName;

Note that I changed LIKE to = since the Like operator is needed only if you're
using wildcards in a text string.
 
Back
Top