Stacked Bar Chart Trouble!

[Buckar00B0nzai]

I have a stacked bar chart that is supposed to display the volume of different service request types (e.g. "Promotion Record Request" or "Travel Document Support") per month. The criteria for the underlying criteria (date range, etc.) is pulled from a "criteria form" that works great for text reports. However, when I attempt to open the bar chart report, I get this error message: "The Microsoft Access database engine does not recognize 'Forms![Graphic Report Selection Form]!Text33' as a valid field name or expression."

The SQL for my query is:

SELECT [Completed Requests Table].[DATE/TIME COMPLETED], [Completed Requests Table].[REQUESTOR ORG], [Completed Requests Table].TECHNICIAN, [Completed Requests Table].DELIVERY_RATING, [Completed Requests Table].[SERVICE REQUESTED]
FROM [Completed Requests Table]
WHERE ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35]) AND (([Completed Requests Table].[REQUESTOR ORG])=[Forms]![Graphic Report Selection Form]![Text41]) AND (([Completed Requests Table].TECHNICIAN)=[Forms]![Graphic Report Selection Form]![Text39]) AND (([Completed Requests Table].DELIVERY_RATING)=[Forms]![Graphic Report Selection Form]![Text46])) OR ((([Completed Requests Table].[REQUESTOR ORG])=[Forms]![Graphic Report Selection Form]![Text41]) AND (([Completed Requests Table].TECHNICIAN)=[Forms]![Graphic Report Selection Form]![Text39]) AND (([Completed Requests Table].DELIVERY_RATING)=[Forms]![Graphic Report Selection Form]![Text46]) AND (([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null)) OR ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35]) AND (([Completed Requests Table].TECHNICIAN)=[Forms]![Graphic Report Selection Form]![Text39]) AND (([Completed Requests Table].DELIVERY_RATING)=[Forms]![Graphic Report Selection Form]![Text46]) AND (([Forms]![Graphic Report Selection Form]![Text41]) Is Null)) OR ((([Completed Requests Table].TECHNICIAN)=[Forms]![Graphic Report Selection Form]![Text39]) AND (([Completed Requests Table].DELIVERY_RATING)=[Forms]![Graphic Report Selection Form]![Text46]) AND (([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text41]) Is Null)) OR ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35]) AND (([Completed Requests Table].[REQUESTOR ORG])=[Forms]![Graphic Report Selection Form]![Text41]) AND (([Completed Requests Table].DELIVERY_RATING)=[Forms]![Graphic Report Selection Form]![Text46]) AND (([Forms]![Graphic Report Selection Form]![Text39]) Is Null)) OR ((([Completed Requests Table].[REQUESTOR ORG])=[Forms]![Graphic Report Selection Form]![Text41]) AND (([Completed Requests Table].DELIVERY_RATING)=[Forms]![Graphic Report Selection Form]![Text46]) AND (([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text39]) Is Null)) OR ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35]) AND (([Completed Requests Table].DELIVERY_RATING)=[Forms]![Graphic Report Selection Form]![Text46]) AND (([Forms]![Graphic Report Selection Form]![Text41]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text39]) Is Null)) OR ((([Completed Requests Table].DELIVERY_RATING)=[Forms]![Graphic Report Selection Form]![Text46]) AND (([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text41]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text39]) Is Null)) OR ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35]) AND (([Completed Requests Table].[REQUESTOR ORG])=[Forms]![Graphic Report Selection Form]![Text41]) AND (([Completed Requests Table].TECHNICIAN)=[Forms]![Graphic Report Selection Form]![Text39]) AND (([Forms]![Graphic Report Selection Form]![Text46]) Is Null)) OR ((([Completed Requests Table].[REQUESTOR ORG])=[Forms]![Graphic Report Selection Form]![Text41]) AND (([Completed Requests Table].TECHNICIAN)=[Forms]![Graphic Report Selection Form]![Text39]) AND (([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text46]) Is Null)) OR ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35]) AND (([Completed Requests Table].TECHNICIAN)=[Forms]![Graphic Report Selection Form]![Text39]) AND (([Forms]![Graphic Report Selection Form]![Text41]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text46]) Is Null)) OR ((([Completed Requests Table].TECHNICIAN)=[Forms]![Graphic Report Selection Form]![Text39]) AND (([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text41]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text46]) Is Null)) OR ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35]) AND (([Completed Requests Table].[REQUESTOR ORG])=[Forms]![Graphic Report Selection Form]![Text41]) AND (([Forms]![Graphic Report Selection Form]![Text39]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text46]) Is Null)) OR ((([Completed Requests Table].[REQUESTOR ORG])=[Forms]![Graphic Report Selection Form]![Text41]) AND (([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text39]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text46]) Is Null)) OR ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35]) AND (([Forms]![Graphic Report Selection Form]![Text41]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text39]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text46]) Is Null)) OR ((([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text41]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text39]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text46]) Is Null));

Can anyone help?

 

[dhookom]

The Row Source of your query is probably a crosstab query which requires you to enter the data type of all parameters. You have a mess of them with all kinds of non-descript names. Select Query->Parameters and enter something like:
[tt][blue]
Forms![Graphic Report Selection Form]!Text33 Date/time
[Forms]![Graphic Report Selection Form]![Text35] Date/time
[/blue][/tt]
I would consider building the SQL on the fly based on what might have a value in the form controls. This would get rid of all references to form controls in the actual sql.


Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

BTW: you should get rid of all the default control names and change them to some name that has meaning.

Duane
Hook'D on Access
MS Access MVP

 

[Buckar00B0nzai]

Thanks dhookum. So - is my query the issue or is there something that I can do to the chart?

The chart SQL is:

TRANSFORM Count(*) AS [Count] SELECT (Format([DATE/TIME COMPLETED],"MMM 'YY")) FROM [Completed Graphic Search Query] GROUP BY (Year([DATE/TIME COMPLETED])*12 + Month([DATE/TIME COMPLETED])-1),(Format([DATE/TIME COMPLETED],"MMM 'YY")) PIVOT [SERVICE REQUESTED];

 

[Buckar00B0nzai]

Alright - and I simplified the search criteria to only include a date range. New SQL looks like:

SELECT [Completed Requests Table].[DATE/TIME COMPLETED], [Completed Requests Table].[REQUESTOR ORG], [Completed Requests Table].TECHNICIAN, [Completed Requests Table].DELIVERY_RATING, [Completed Requests Table].[SERVICE REQUESTED]
FROM [Completed Requests Table]
WHERE ((([Completed Requests Table].[DATE/TIME COMPLETED]) Between [Forms]![Graphic Report Selection Form]![Text33] And [Forms]![Graphic Report Selection Form]![Text35])) OR ((([Forms]![Graphic Report Selection Form]![Text33]) Is Null) AND (([Forms]![Graphic Report Selection Form]![Text35]) Is Null));

I am still getting the error message stating that the engine does not recognize Text33 (start of date range).

Can anyone help?

 

[Buckar00B0nzai]

And I followed the PARAMETERS guidance and got this:

PARAMETERS [Forms]![Graphic Report Selection Form]![Text33 ] DateTime, [Forms]![Graphic Report Selection Form]![Text35] DateTime;
SELECT [Completed Requests Table].[DATE/TIME COMPLETED], [Completed Requests Table].[REQUESTOR ORG], [Completed Requests Table].TECHNICIAN, [Completed Requests Table].DELIVERY_RATING, [Completed Requests Table].[SERVICE REQUESTED]
FROM [Completed Requests Table];

When I type a specific start (text33) and end date (text35), the graph does not change. It displays all data for all dates.

Suggestions?

 

[dhookom]

Putting the control references in the Parameters only helps the query determine what your data types are. You must still keep the criteria if you want to filter the records.

Duane
Hook'D on Access
MS Access MVP