Crosstab Query Chart Issue

[james28]

Hello All,

I have a select query that works just fine. As one of the criteria, I have [Forms]![SearchForm]![TxtSearchDate] . If I have "SearchForm" open and a date in "TxtSearchDate", I can run the query and it works just fine.


Basic Option:
If I create a regular column chart from the query that is looking at 1 value, it also works just fine. For example, in my query, I have a column that is animal (cat, dog, fish, etc). My chart shows the each animal as one bar, with the number of each animal represented.

Advanced Option:
I am running into problems when I create a stacked column chart that uses an additional value. For example, in my query, I have a column that is animal (cat, dog, fish, ect) and another column that is State (NY or PA). My chart should show each animal as one bar, with the bar being segmented into two parts, one for each state.



If I do not list the field as criteria for the query, everything works as I expect it to run and I am able to create the stacked column chart.

When I introduce the field criteria, I get an error saying, "The Microsoft Access database engine does not recognize '[Forms]![SearchForm]![TxtSearchDate]' as a valid field name or expression.




If I change the row source of the chart to a select (instead of a cross tab), the chart is able to display without any errors and it loads using [Forms]![SearchForm]![TxtSearchDate]; however, it is only the chart that is explained in Basic Option above.



My goal is to have the chart explained in Advanced Option display with a field that allows me to specify the date.

I would appreciate any guidance in resolving this issue. Thank you!

[pbaldy]

Crosstab queries require the parameters be explicitly declared. I think it's on the ribbon in 2010.

[james28]

Paul,

Thank you for the reply. I was able to find the parameters area on the ribbon; however, I can not seem to make anything function. As far as the parameter goes, I am putting [Forms]![SearchForm]![TxtSearchDate] in as the parameter and I have tried both long integer and date/time in the data type. Is there a different way that I should be entering the parameter?


Thank you again!

[pbaldy]

I would expect that to work. Here's an A2k link, but other versions would work the same, other than how to get at the parameters:

http://support.microsoft.com/default...b;en-us;209778

What's the SQL of the query now? It should start with Parameters now.

[james28]

Paul,

Thank you for the reply. I see where I went wrong. I was adding the parameter to the graph, not to the query. Thank you for the clarification. It is working now!

[pbaldy]

Excellent! Happy to help.