Crosstab Query Error

Topic

New Reply

Hi,
I’m trying to run a crosstab query that uses another query to provide the information. The first query is sorted by a a control [txtYear] on frmReports. This works fine. This query is a query that calculates totals.

When I go to run the new crosstab query I get the error that I’ve uploaded here. I don’t understand why I’m getting the error since the control filters the first query with no problem.

Thanks,
Leesha

Reply | Quote

Replies

Are you creating this using the Crosstab wizard or by changing the query type to Crosstab. The error message indicates that you haven’t set either the Row Heading or the Column Heading in the design. Crosstab queries add a second row (below the Totals row if it is on), and you have to specify one or more Row Headings, one Column Heading and one Value. Hope that helps.

Reply | Quote

Hi!

I’m not doing it from the wizard, however I do have all of the rows, columns and values identified. Nothing I try works. I use crosstabs all the time so this one really has me stumped. It runs fine until I put in the filter of [forms]![frmReports]![txtyear]. If I don’t try to filter the year its OK. If I use this same filter on a select query it runs fine and filters as it should. It’s only when I try to do it in a crosstab query.

Leesha

Reply | Quote

Try putting the [forms]![frmReports]![txtyear] in a separate cell where Year is the field and that is the criteria, and put a WHERE on that column. If that doesn’t do it, you may need to put it in the Select query you are basing the crosstab on. If that stumps you, try giving us the SQL statement for your Select and your Crosstab.

Reply | Quote

Did you specify your parameter field as a parameter in the query properties? [forms]![frmReports]![txtyear] in the parameter side and the datatype (either date or integer depending on what is in that field). Crosstabs will fail without these specified.

Peter

Reply | Quote