Solved Crosstab Query - does not recognize as valid field name or expression

[jack555]

while creating a crosstab query throws below error. The source is another query having a calculation. please help how to rectify this.

Crosstab Query

PARAMETERS qryKPIDataEntered.KPI_Code Text ( 255 );
TRANSFORM First(qryDataSequence.Calculation) AS FirstOfCalculation
SELECT qryDataSequence.KPI_Title, qryDataSequence.Target
FROM qryDataSequence
GROUP BY qryDataSequence.KPI_Title, qryDataSequence.Target
PIVOT qryDataSequence.EntrySequence;

Source Query - qryDataSequence

SELECT (SELECT COUNT(*)+1   
FROM qryKPIDataEntered AS T2   
WHERE T2.KPI_code = qryKPIDataEntered.KPI_code AND qryKPIDataEntered.Action_Date < T2.Action_Date) AS EntrySequence, qryKPIDataEntered.*
FROM qryKPIDataEntered;

Error as below. I have added a parameter to crosstab query as someone suggested but still, I am missing something.

*minor edit was done to add source query name.

 

[pbaldy]

Unless I'm blind, the source query does not return that date field, so it can't be used in the criteria.

 

[jack555]

Unless I'm blind, the source query does not return that date field, so it can't be used in the criteria.

Thanks for the reply. It does return the date field and in the date format only.

 

[pbaldy]

Sorry, I mixed up which was the base query. Have you tried adding a parameter on that date field?

 

[jack555]

Instead of building a crosstab query from another query having a calculated field, I created a make table query and done the crosstab from the table. it works as intended. I believe the issue with calculated field query. Had a workaround, but helps for me.

 

[pbaldy]

Glad you found a solution.