Crosstab Query Filter Error

[Leesha]

Hi,

I have a crosstab query that I'm trying to filter based on two date controls on frmReports. When I filter use Between [forms]![frmReports]![txtFrom] And [forms]![frmReports]![txtTo] in a select query it works find. The minute I convert the query to a crosstab query I get a an error that [forms]![frmReports]![txtFrom] is not a valid field. I've ensured that all of the fields are labeled correctly as row, column or value. I'm not sure what to do next.

Thanks,
Leesha

[Wendell]

I think you will have better luck if you create a select query that does the filtering and then base the Crosstab query on that query. Crosstab queries operate rather differently than select queries, and in this case I am guessing you have the date field you are filtering as a column header in the crosstab. If not, provide us a bit more detail and we'll try to help.

[Leesha]

Hi,

I tried using the select to do the filtering and then base the crosstab off of it and still get the same error. The column head is not the date field. That is set as a Row field. The query runs fine with the date filter in the select query and it runs fine as a crosstab as long as I take out the date filter.

Leesha

[Wendell]

I think there is another issue at work here - it seems to me that crosstabs don't like parameter prompts. I think you may need to modify the QueryDef using the values in the form, and then run the crosstab. I think HansV made a post on that subject fairly recently. I'll do a search on crosstabs and see what I find. [Edited] OK, you need to define the paramemters you are using in order to get the crosstab to behave - see this thread on Windows Secrets or you have to resort to doing a change to the Query Def before you run the crosstab.

[Leesha]

OMG!! Bingo!! That did it!!

Thank you!
Leesha

[JohnH]

For Crosstabs with Parameters (whether directly in the crosstab, or in a Select Query behind the crosstab) explicitly setting the parameters in the Query Parameters Dialog us usually enough to fix the error.