Crosstab User Entered Parameter Problem 2

[Darrylles]

I have a crosstab query that obviously uses another query to get it's data.
The first query asks the user for an order number value via [Enter Work Order#:] in the Criteria.

When my crosstab query is run (which runs the offending query) I get this error message:

The Microsoft Jet Database Engine does not recognise [Enter Work Order#:] as a valid field name or expression.

If I run the first query, then it asks me for a Work Order# as intended.

(I use both queries to produce a report with ALL values with no problems. I've copied and renamed these and simply added the criteria to query the user for a WorkOrder number to get data for a single workorder - I get this error).

I'm new to crosstabs - any ideas out there?

Thanks in advance.

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Ally72]

When you used crosstab queries with parameters, you need to declare them in "Query Parameters". (Right hand click on the top half of query in design view, or go to Query > Parameters).

Parameter names need to be entered exactly as they are in the query and to make sure the the data types are correct.

HTH - Ally

 

[Darrylles]

Hi,

Thanks Ally - perfect, you probably saved me days of messing.

I specified the parameter in the first simple query (which is called by the crosstab) - worked fine.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Henryp]

Useful post which solved half of my problem too. However, I am now stuck on how to use a similar query as the basis of a report. In my case the parameter is the company name, and the crosstab query shows order types in rows and offices (by town) as the columns. (Each company has more than one office, and the number of towns overall is very large, although most companies will have a maximum of 20 offices).

However when I try to create a report based on the query, there are no fields to select - because the fields aren't generated (ie the relevant towns aren't know) until the parameter is entered!

I have a feeling that there is a simple way around this - any ideas?

thanks