Crosstab with parameter doesn't work

[Tony Wainwright]

Using Access 2003
I am building an application that presents data in the form of CrossTab
queries. I need to parameterise these queries so I can filter out
extraneous information. By query is as below:

TRANSFORM Sum(tblSoap.LocalSellPrice) AS SumOfLocalSellPrice
SELECT luMarket.Market, tblSoap.Name
FROM tblSoap INNER JOIN luMarket ON tblSoap.Market = luMarket.MarketID
WHERE (((luMarket.MarketID)=[Forms]![frmDrillDownEURegion]![cboCountry]))
GROUP BY luMarket.Market, tblSoap.Name, luMarket.MarketID
PIVOT tblSoap.[12Month];

This is the simplest query in the application. As I get further on there
will be more and more parameters added.

On running this query I get the following error:
Jet database does not recognise
'[Forms]![frmDrillDownEURegion]![cboCountry]' as a valid field or expression
Why won't Access recognise this? Is it restriction built-in to CrossTabs?
Is there any way round this?

Tony

 

[Nikos Yannacopoulos]

Tony,

Unlike all other query types, crosstabs require that parameters be
explicitly declared as such in the query design under menu item Query >
Parameters. Copy and paste your parameter expression in there and select the
appropriate data type, and your problem wil be solved.

HTH,
Nikos

 

[Tony Wainwright]

Thanks Nikos

Tony,

Unlike all other query types, crosstabs require that parameters be
explicitly declared as such in the query design under menu item Query >
Parameters. Copy and paste your parameter expression in there and select the
appropriate data type, and your problem wil be solved.

HTH,
Nikos

Using Access 2003
I am building an application that presents data in the form of CrossTab
queries. I need to parameterise these queries so I can filter out
extraneous information. By query is as below:

TRANSFORM Sum(tblSoap.LocalSellPrice) AS SumOfLocalSellPrice
SELECT luMarket.Market, tblSoap.Name
FROM tblSoap INNER JOIN luMarket ON tblSoap.Market = luMarket.MarketID
WHERE (((luMarket.MarketID)=[Forms]![frmDrillDownEURegion]![cboCountry]))
GROUP BY luMarket.Market, tblSoap.Name, luMarket.MarketID
PIVOT tblSoap.[12Month];

This is the simplest query in the application. As I get further on there
will be more and more parameters added.

On running this query I get the following error:
Jet database does not recognise
'[Forms]![frmDrillDownEURegion]![cboCountry]' as a valid field or expression
Why won't Access recognise this? Is it restriction built-in to CrossTabs?
Is there any way round this?

Tony