Access Query Optimizer error

[bnhcomputing]

Using Access 2007, SP1.

I have a Query that consists of joins, sub-queries, and joins to saved
queries. When I execute the Query it returns a recordset, no errors of any
kind.

Now, once I use that query as a source to a crosstab query, the crosstab
query does not execute.

The error I receive states that the database engine does not recognize 'a
specific field name' as a valid field name or expresion. This particular
field name is only referenced in a saved query, it is not in my source query
for the crosstab or the crosstab query itself.

Given the source query execute without error, this points to a bug in the
access query optimization.

Anybody else seen this?

 

[Jeff Boyce]

Any chance a selection criterion/parameter is involved? You might need to
explicitly declare that parameter in the underlying query.

I've run into something similar in cross-tabs...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer (MVP)]

Perhaps the following applies.

With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[bnhcomputing]

I'm not using and parameters in my queries, so parameters are not the issue.

 

[ntc]

consider to not make the crosstab query sourced on that query....

instead change that query into a MakeTable query to create a temp table.

then source that crosstab on this new temp table...

less efficient but this puts a logical firewall between the two...that can
allow you to experiment with your cross tab creation

 

[bnhcomputing]

Putting the output of the first query into a table and then making a crossab
with the table as the source does produce the desired output.

This definately points to a BUG in the Access Query Optimizer then. Anybody
know how to submit a BUG report to Microsoft?