charts based on parameter queries

[Thos @ fodc]

I have a report that displays several charts of data over time (data on y
axis, date on x axis) on each page, one page per site. The data is from a
query of all data on all dates at all sites. I created a new query with a
parameter for start and end date. The query works fine by itself, but when
I run the report with charts based on the new query, it asks me for the
startDate and endDate parameter values like normal, but then gives an error:

"The Microsoft Jet database engine does not recognize '[dateStart]' as a
valid field name or expression."

After I say OK to that error, I get another popup:

"An error occurred while sending data to the OLE server (the application
used to create the object)."

Any ideas? Is it possible to created linked charts based on a parameter
query?

T

 

[Thos @ fodc]

FYI, the parameter query (qData) is like so:

SELECT Site, Date, Data1, Data2, Data3
FROM Table
WHERE Date>[dateStart] And Date<[dateEnd]

That is the query that the report is based on.

One of the chart queries is like so:

TRANSFORM First(qData.Data1) AS Data1
SELECT (Format([Date],"mmm"" '""yy")) AS Expr1
FROM qData
GROUP BY (Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy"))
PIVOT qData.Site;

Any ideas how to make this work better? Basically, I want to run the report
and have a popup asking for the begin and end date range, then I want the
report to show the three charts (one for each Data field) for each site per
page.

T

 

[Thos @ fodc]

I fixed the error by explicitly defining the query parameters in the query
editor window. However, now I have to enter the parameter values once for
the report, and twice for EACH chart on the report page. There will be up 9
charts per page, so there must be a better way to do this. Any suggestions?

T

FYI, the parameter query (qData) is like so:

SELECT Site, Date, Data1, Data2, Data3
FROM Table
WHERE Date>[dateStart] And Date<[dateEnd]

That is the query that the report is based on.

One of the chart queries is like so:

TRANSFORM First(qData.Data1) AS Data1
SELECT (Format([Date],"mmm"" '""yy")) AS Expr1
FROM qData
GROUP BY (Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy"))
PIVOT qData.Site;

Any ideas how to make this work better? Basically, I want to run the
report and have a popup asking for the begin and end date range, then I
want the report to show the three charts (one for each Data field) for
each site per page.

T

I have a report that displays several charts of data over time (data on y
axis, date on x axis) on each page, one page per site. The data is from a
query of all data on all dates at all sites. I created a new query with a
parameter for start and end date. The query works fine by itself, but
when I run the report with charts based on the new query, it asks me for
the startDate and endDate parameter values like normal, but then gives an
error:

"The Microsoft Jet database engine does not recognize '[dateStart]' as a
valid field name or expression."

After I say OK to that error, I get another popup:

"An error occurred while sending data to the OLE server (the application
used to create the object)."

Any ideas? Is it possible to created linked charts based on a parameter
query?

T

 

[Duane Hookom]

Kick your development up a notch and abandon parameter prompts for queries.
Always use controls on forms to allow users to enter criteria for forms and
reports.

--
Duane Hookom
MS Access MVP

I fixed the error by explicitly defining the query parameters in the query
editor window. However, now I have to enter the parameter values once for
the report, and twice for EACH chart on the report page. There will be up
9 charts per page, so there must be a better way to do this. Any
suggestions?

T

FYI, the parameter query (qData) is like so:

SELECT Site, Date, Data1, Data2, Data3
FROM Table
WHERE Date>[dateStart] And Date<[dateEnd]

That is the query that the report is based on.

One of the chart queries is like so:

TRANSFORM First(qData.Data1) AS Data1
SELECT (Format([Date],"mmm"" '""yy")) AS Expr1
FROM qData
GROUP BY (Year([Date])*12+Month([Date])-1), (Format([Date],"mmm""
'""yy"))
PIVOT qData.Site;

Any ideas how to make this work better? Basically, I want to run the
report and have a popup asking for the begin and end date range, then I
want the report to show the three charts (one for each Data field) for
each site per page.

T

I have a report that displays several charts of data over time (data on y
axis, date on x axis) on each page, one page per site. The data is from
a query of all data on all dates at all sites. I created a new query
with a parameter for start and end date. The query works fine by itself,
but when I run the report with charts based on the new query, it asks me
for the startDate and endDate parameter values like normal, but then
gives an error:

"The Microsoft Jet database engine does not recognize '[dateStart]' as a
valid field name or expression."

After I say OK to that error, I get another popup:

"An error occurred while sending data to the OLE server (the application
used to create the object)."

Any ideas? Is it possible to created linked charts based on a parameter
query?

T