Crosstab query criteria from subform control (1 Viewer)

Hi, I am trying to run a crosstab query with criteria based on a subform control:

TRANSFORM Count([CHIN Sample Data].Species) AS CountOfSpecies
SELECT [CHIN Sample Data].Species, [CHIN Sample Data].Water, [CHIN Sample Data].Section, [CHIN Sample Data].Date, [CHIN Sample Data].Mark, [CHIN Sample Data].SurveyID, [CHIN Sample Data].Species
FROM [CHIN Sample Data]
WHERE ((([CHIN Sample Data].SurveyID)=Forms![SGS App Form]![SGS Survey Form].Form![SurveyID]) And (([CHIN Sample Data].Species)=1))
GROUP BY [CHIN Sample Data].SurveyID, [CHIN Sample Data].Species, [CHIN Sample Data].Water, [CHIN Sample Data].Section, [CHIN Sample Data].Date, [CHIN Sample Data].Mark
PIVOT [CHIN Sample Data].Sex;

Main Form = SGS App Form
Subform = SGS Survey Form
Control = SurveyID

This syntax works fine for other simple select queries I built to test it, but the Crosstab query returns the following error:

The Microsoft Access database engine does not recognize as a valid field name or expression.

Is it possible to to supply criteria from a form (or subform) for a Crosstab Query?

Thanks,

If you can successfully create a simple select query that leverages the value in the subform control, then do so, and save it, maybe as QueryA. Then, rather than write your Crosstab query against the table [CHIN Sample Data], write it against your previously saved QueryA.

See if that works.

hth
Mark

Thanks. Sounded like a great workaround, but it is still producing the same error on the Crosstab.

TRANSFORM Count([CHIN Sample Data].Species) AS CountOfSpecies
SELECT [CHIN Sample Data].Species, [CHIN Sample Data].Water, [CHIN Sample Data].Section, [CHIN Sample Data].Date, [CHIN Sample Data].Mark, [CHIN Sample Data].Species
FROM [CHIN Sample Data]
WHERE ((([CHIN Sample Data].Species)=1))
GROUP BY [CHIN Sample Data].Species, [CHIN Sample Data].Water, [CHIN Sample Data].Section, [CHIN Sample Data].Date, [CHIN Sample Data].Mark
PIVOT [CHIN Sample Data].Sex;

The select query executes fine with the criteria.

If you open the crosstab query in SQL design view, and then try to run it from there, is the cursor moved to the location of the error? This can sometimes be a way to find where the error is in SQL that has an error.

To be sure, place the cursor before the first character in the first line of the SQL. Then click the ribbon to run the SQL. Observe where the cursor is in the SQL after the execution fails.

for some queries, including crosstabs, you need to declare your parameters (always a good idea anyway) - see the parameters option on the ribbon in query design view - your crosstab should look something like

It doesn't matter whether the parameter is in the crosstab query or in a query that is used by the crosstab, you will still have to define the parameters explicitly.

I think the crosstab is the only "odd man out". So the MS developer who built the code for crosstabs either read the memo and followed the rules or the others did and he didn't. This has been different (and annoying) for as long as I can remember.