Cross Tab Filter ?

  • Thread starter Thread starter Scott Duncan
  • Start date Start date
S

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
 
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
 
Nikos,

It works !

Thank you,

SD

Nikos Yannacopoulos said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top