Crosstab Delima

[nvus]

I find myself using crosstab queries frequently and am baffled about this one issue. If anyone as insight to this, I would love to hear it.

For the recordsource for the crosstab, I use a query, for this example, I will call it query1. query1 will have the following criteria in a date field:

Between Forms!NameOfForm!StartOfDateField and Forms!NameOfForm!EndOfDateField

However when I attempt to run the crosstab, I get the following msg:

"The Microsoft Jet Database does not recognize Forms!NameOfForm!StartOfDateField as a valid field name or expression.

I know it's valid as I can run many other queries, that the logic was copied out of and they all work.

I've also attempted the same criteria within the crosstab using a WHERE statement, but it returns the same msg.

I can still accomplish what I'm after by running make table queries and using the tables as the recordsources for the crosstabs, but I shouldn't have to do that.

What seems to be the issue with the crosstab queries that prohibit the ability to call a criteria such as this.

Thanks in advance for any insight.

p.s. and yes the form that holds the DATES is OPEN

 

[Jon K]

You need to declare the data type of the two parameters for the crosstab.

Open the query that contains Forms!NameOfForm!StartOfDateField and Forms!NameOfForm!EndOfDateField in query design view. Choose menu Query, Parameters...

In the dialog that pops up, type the two parameters in the left column and select their correct data type in the right column. Click OK to close the dialog.
.

 

[nvus]

Thanks very much....works like a charm

 

[Pat Hartman]

Unlike other query types, Crosstabs are very particular about having parameters specifically defined. You will run into this whether the the parameter is used in the crosstab itself or in a query that the crosstab uses.

 

[Dennisk]

Also it does not seem matter where you declare the parameters. If your crosstab uses query y then you could declare the parameters in the crosstab query itself, or query y.