A2K7 doesn't seem to be as forgiving as earlier versions when running
queries based on parameter queries in the context of a recordset. The
parameter query runs fine by itself, even within a recordset, but
Access gets confused when it is used by a second query, one also
within a recordset. I have several crosstab queries that are based on
a parameter query that obtains a date within the desired week from a
form. Instead of using ADODB to supply parameters explicitly, I ended
up changing the SQL of the querydef for the parameter query to reflect
the actual date needed and eliminated using the parameter.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

It may be that you already know this, but just in case.

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

If you are building the SQL String.
Parameters [Name of Parameter] Text(255), [Some Date parameter] DateTime;
SELECT xxx
FROM ...

Parameter line requires the name of the parameter and the data type.

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

On 5/23/2011 9:35 AM, James A. Fortune wrote: