Cross Tab Filter ?

[Scott Duncan]

We want to be able to Filter a Cross Tab Query with the expression
[Screen].[ActiveForm]![FieldName]

Here is a basic Query that we can not get to work...

TRANSFORM Count([Order Entry].[Sales Order Number]) AS [CountOfSales Order
Number]
SELECT [Order Entry].[Customer Id], Customers.Closed, Count([Order
Entry].[Sales Order Number]) AS Total
FROM [Order Entry] INNER JOIN Customers ON [Order Entry].[Customer Id] =
Customers.[Customer Id]
WHERE (([Order Entry].[Customer Id]=[Screen].[ActiveForm]![Customer Id]))
GROUP BY [Order Entry].[Customer Id], Customers.Closed
ORDER BY [Order Entry].[Customer Id]
PIVOT [Order Entry].[Order Type];

Jet does not recognize as a valid field name or expression.

We have tried to pre-filter with a Query based upon the Order Entry Table
where
[Order Entry].[Customer Id]=[Screen].[ActiveForm]![Customer Id] and we get
the same error
message.

Any thoughts would be greatly appreciated.

TIA,

SD

 

[Nikos Yannacopoulos]

Scott,

Contratry to other query types, Crosstabs require parameters to be
explicitly declared. Open the query in design view, go Query > Parameters
and paste the parameter expression ([Screen].[ActiveForm]![FieldName]) in
the first available line, selecting the right data type. This should solve
your problem.

HTH,
Nikos

 

[Scott Duncan]

Nikos,

It works !

Thank you,

SD

Scott,

Contratry to other query types, Crosstabs require parameters to be
explicitly declared. Open the query in design view, go Query > Parameters
and paste the parameter expression ([Screen].[ActiveForm]![FieldName]) in
the first available line, selecting the right data type. This should solve
your problem.

HTH,
Nikos

We want to be able to Filter a Cross Tab Query with the expression
[Screen].[ActiveForm]![FieldName]

Here is a basic Query that we can not get to work...

TRANSFORM Count([Order Entry].[Sales Order Number]) AS [CountOfSales
Order
Number]
SELECT [Order Entry].[Customer Id], Customers.Closed, Count([Order
Entry].[Sales Order Number]) AS Total
FROM [Order Entry] INNER JOIN Customers ON [Order Entry].[Customer Id] =
Customers.[Customer Id]
WHERE (([Order Entry].[Customer Id]=[Screen].[ActiveForm]![Customer Id]))
GROUP BY [Order Entry].[Customer Id], Customers.Closed
ORDER BY [Order Entry].[Customer Id]
PIVOT [Order Entry].[Order Type];

Jet does not recognize as a valid field name or expression.

We have tried to pre-filter with a Query based upon the Order Entry Table
where
[Order Entry].[Customer Id]=[Screen].[ActiveForm]![Customer Id] and we
get
the same error
message.

Any thoughts would be greatly appreciated.

TIA,

SD