Chart does not recognize my query criteria

  • Thread starter Thread starter fcmedina78
  • Start date Start date
F

fcmedina78

I have a report that includes a chart based on a query. When I type the
actual criteria into the query it the chart works without issue. When I
designed a form for the user to enter the crieteria and then directed the
query's criteria to that form the queries worked without issue but the chart
came back with an error saying that Jet did not recognize my criteria. I
tried renaming the fields in the reports without any luck. Here are the
details:

This is the query I am running:

SELECT tblWinEntry.[Store Number], Sum(tblWinEntry.[Win Amount]) AS
[SumOfWin Amount], DatePart("q",[DateEntry]) AS Quarter,
DatePart("yyyy",[DateEntry]) AS [Year]
FROM tblWinEntry
GROUP BY tblWinEntry.[Store Number], DatePart("q",[DateEntry]),
DatePart("yyyy",[DateEntry])
HAVING
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompFirstYrSelect]))
OR
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompSecondYrSelect]))
OR
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompThirdYrSelect]));

The above query works fine by itself or on a regular report, I only get the
error when I try to base a chart on it. Any Idea's?
 
I expect the row source property of your chart might be a crosstab query. If
so, you must declare the data types of the criteria. This is done in the
Query->Parameters and entering:
[Forms]![frmCompReportSelection]![QuarterCompFirstYrSelect] Integer
etc
 
Duane,

I went back and defned the criteria and I am still getting the same result,
I can create a report based on the query, run the query in datasheet view and
all have corect an accurate data but when create a chart from it I get an
error saying that Jet does not recognize my criteria as a valid field name or
expression???

Duane Hookom said:
I expect the row source property of your chart might be a crosstab query. If
so, you must declare the data types of the criteria. This is done in the
Query->Parameters and entering:
[Forms]![frmCompReportSelection]![QuarterCompFirstYrSelect] Integer
etc

--
Duane Hookom
Microsoft Access MVP


fcmedina78 said:
I have a report that includes a chart based on a query. When I type the
actual criteria into the query it the chart works without issue. When I
designed a form for the user to enter the crieteria and then directed the
query's criteria to that form the queries worked without issue but the chart
came back with an error saying that Jet did not recognize my criteria. I
tried renaming the fields in the reports without any luck. Here are the
details:

This is the query I am running:

SELECT tblWinEntry.[Store Number], Sum(tblWinEntry.[Win Amount]) AS
[SumOfWin Amount], DatePart("q",[DateEntry]) AS Quarter,
DatePart("yyyy",[DateEntry]) AS [Year]
FROM tblWinEntry
GROUP BY tblWinEntry.[Store Number], DatePart("q",[DateEntry]),
DatePart("yyyy",[DateEntry])
HAVING
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompFirstYrSelect]))
OR
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompSecondYrSelect]))
OR
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompThirdYrSelect]));

The above query works fine by itself or on a regular report, I only get the
error when I try to base a chart on it. Any Idea's?
Click to expand...
Click to expand...
 
Please provide the SQL view of the Row Source of the chart control. Possibly
provide the SQL if the source of the Row Source is a previous query.

--
Duane Hookom
Microsoft Access MVP


fcmedina78 said:
Duane,

I went back and defned the criteria and I am still getting the same result,
I can create a report based on the query, run the query in datasheet view and
all have corect an accurate data but when create a chart from it I get an
error saying that Jet does not recognize my criteria as a valid field name or
expression???

Duane Hookom said:
I expect the row source property of your chart might be a crosstab query. If
so, you must declare the data types of the criteria. This is done in the
Query->Parameters and entering:
[Forms]![frmCompReportSelection]![QuarterCompFirstYrSelect] Integer
etc

--
Duane Hookom
Microsoft Access MVP


fcmedina78 said:
I have a report that includes a chart based on a query. When I type the
actual criteria into the query it the chart works without issue. When I
designed a form for the user to enter the crieteria and then directed the
query's criteria to that form the queries worked without issue but the chart
came back with an error saying that Jet did not recognize my criteria. I
tried renaming the fields in the reports without any luck. Here are the
details:

This is the query I am running:

SELECT tblWinEntry.[Store Number], Sum(tblWinEntry.[Win Amount]) AS
[SumOfWin Amount], DatePart("q",[DateEntry]) AS Quarter,
DatePart("yyyy",[DateEntry]) AS [Year]
FROM tblWinEntry
GROUP BY tblWinEntry.[Store Number], DatePart("q",[DateEntry]),
DatePart("yyyy",[DateEntry])
HAVING
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompFirstYrSelect]))
OR
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompSecondYrSelect]))
OR
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompThirdYrSelect]));

The above query works fine by itself or on a regular report, I only get the
error when I try to base a chart on it. Any Idea's?
Click to expand...
Click to expand...
Click to expand...
 
Duane,

I'm not sure what happened but when I started working on this today I
selected to compact and repair the database and now the chart i working. I'm
not sure what the difference is between today and yesterday and I did the
same thing yesterday with no improvement but I'm assuming it was just a bug
(now that I think about it, it was probably a but and the fact that I didn't
have my criteria defined). Thanks for all your help, for now the problem is
solved.

Duane Hookom said:
Please provide the SQL view of the Row Source of the chart control. Possibly
provide the SQL if the source of the Row Source is a previous query.

--
Duane Hookom
Microsoft Access MVP


fcmedina78 said:
Duane,

I went back and defned the criteria and I am still getting the same result,
I can create a report based on the query, run the query in datasheet view and
all have corect an accurate data but when create a chart from it I get an
error saying that Jet does not recognize my criteria as a valid field name or
expression???

Duane Hookom said:
I expect the row source property of your chart might be a crosstab query. If
so, you must declare the data types of the criteria. This is done in the
Query->Parameters and entering:
[Forms]![frmCompReportSelection]![QuarterCompFirstYrSelect] Integer
etc

--
Duane Hookom
Microsoft Access MVP


:

I have a report that includes a chart based on a query. When I type the
actual criteria into the query it the chart works without issue. When I
designed a form for the user to enter the crieteria and then directed the
query's criteria to that form the queries worked without issue but the chart
came back with an error saying that Jet did not recognize my criteria. I
tried renaming the fields in the reports without any luck. Here are the
details:

This is the query I am running:

SELECT tblWinEntry.[Store Number], Sum(tblWinEntry.[Win Amount]) AS
[SumOfWin Amount], DatePart("q",[DateEntry]) AS Quarter,
DatePart("yyyy",[DateEntry]) AS [Year]
FROM tblWinEntry
GROUP BY tblWinEntry.[Store Number], DatePart("q",[DateEntry]),
DatePart("yyyy",[DateEntry])
HAVING
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompFirstYrSelect]))
OR
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompSecondYrSelect]))
OR
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmCompReportSelection]![QuarterCompThirdYrSelect]));

The above query works fine by itself or on a regular report, I only get the
error when I try to base a chart on it. Any Idea's?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top