PARAMETERS error

[darkhat01]

I am running a cross tab on a Union query and I am getting and error.
The Microsoft Jet database engine does not recognize '[Forms]![frmCIOScorecard]![chkMedium]' as a valid field name or expression.

Here is the SQl code:

TRANSFORM Count([CIO Scorecard All Open Risk].IDCount) AS Expr1
SELECT [CIO Scorecard All Open Risk].[Criticality of Risk] AS Expr2, Count([CIO Scorecard All Open Risk].IDCount) AS [Total Of IDCount]
FROM [CIO Scorecard All Open Risk]
WHERE ((([CIO Scorecard All Open Risk].Database)<>"AS OF"))
GROUP BY [CIO Scorecard All Open Risk].[Criticality of Risk]
PIVOT [CIO Scorecard All Open Risk].Database;

So I added : PARAMETERS [Forms]![frmCIOScorecard]![chkMedium] Text ( 255 );
Now I am getting a new error: The Microsoft Jet database engine does not recognize '[Forms]![frmCIOScorecard]![chkHigh]' as a valid field name or expression.

But i am not sure how to fix this error. Any ideas?

Thanks,

Darkhat01

[MikeTheBike]

Hi

I am running a cross tab on a Union query and I am getting and error.
The Microsoft Jet database engine does not recognize '[Forms]![frmCIOScorecard]![chkMedium]' as a valid field name or expression.

Here is the SQl code:

TRANSFORM Count([CIO Scorecard All Open Risk].IDCount) AS Expr1
SELECT [CIO Scorecard All Open Risk].[Criticality of Risk] AS Expr2, Count([CIO Scorecard All Open Risk].IDCount) AS [Total Of IDCount]
FROM [CIO Scorecard All Open Risk]
WHERE ((([CIO Scorecard All Open Risk].Database)<>"AS OF"))
GROUP BY [CIO Scorecard All Open Risk].[Criticality of Risk]
PIVOT [CIO Scorecard All Open Risk].Database;

So I added : PARAMETERS [Forms]![frmCIOScorecard]![chkMedium] Text ( 255 );
Now I am getting a new error: The Microsoft Jet database engine does not recognize '[Forms]![frmCIOScorecard]![chkHigh]' as a valid field name or expression.

But i am not sure how to fix this error. Any ideas?

Thanks,

Darkhat01

I have encounterd the same problem.
It would seem that Access query designer does not like/accept/recognise [Form]![...]![....] parameter type in a cross tab query (as you have stated) UNLESS the cloumns (Field Names) are defoned in the query.

These are defined (listed) using the query designer Properties dialogue in the 'Column Heading' row. Just put your curos in this field/row and press F1 for an expanation.

In SQL view this tranlates to this syntax

PIVOT [CIO Scorecard All Open Risk].Database In ("Field1Name ", "Field2Name ", ... ect);

This also defines the column order.

If you can define the columns you shouls be able to sort it from above, but if you do not know the 'Column' names being returned (because they change with the filter, as has been in some of my cases) then the only solution I have found is the build the query in code and add the query to thr DB using the QueryDefinition collection (but this can raise other problems if you have a single fron end and multi-users)

Not sure if this helps with the solution, but that, I think, is the problem.


MTB

[darkhat01]

Thanks MBT. I am goign to give this a try...

Darkhat01