Error on cross tab qry when using build expression

[ps32208]

I have created a query which works fine but when I create a crosstab query using this query I get an error details below, any help would be great. The error is due to the fact I have built the query to pick the data of an employee depending on which employee is chosen on a button in a form (this all works fine but not the crosstab). When I remove the criteria for picking the appropriate employee '[Forms]![Direct Sales Dashboard]![Employee ID]' the cross tab works fine! Can anyone help?

SQL from original query:

SELECT [Sales Rep].[Sales Rep Name], IIf(InStr([Sales Stage],"Won")>0,"WON",IIf([Committed]="Y","COMMITTED",IIf([Probability]>=50,"UPSIDE","WIP"))) AS [Forecast Flag], Sum(tbl_funnel_by_month_summary.[Forecast Value USD]) AS [SumOfForecast Value USD], tbl_date_index.[Financial Quarter]
FROM tbl_date_index INNER JOIN ([Sales Rep] INNER JOIN tbl_funnel_by_month_summary ON [Sales Rep].[Employee ID] = tbl_funnel_by_month_summary.[Employee ID]) ON tbl_date_index.Month = tbl_funnel_by_month_summary.[Forecast Month]
GROUP BY [Sales Rep].[Sales Rep Name], IIf(InStr([Sales Stage],"Won")>0,"WON",IIf([Committed]="Y","COMMITTED",IIf([Probability]>=50,"UPSIDE","WIP"))), tbl_date_index.[Financial Quarter], tbl_funnel_by_month_summary.[Employee ID]
HAVING (((tbl_funnel_by_month_summary.[Employee ID])=[Forms]![Direct Sales Dashboard]![Employee ID]));

SQL From Crosstab query:

TRANSFORM Sum(error_qry_funnel_forecast_flag_.[SumOfForecast Value USD]) AS [SumOfSumOfForecast Value USD]
SELECT error_qry_funnel_forecast_flag_.[Sales Rep Name], error_qry_funnel_forecast_flag_.[Forecast Flag], Sum(error_qry_funnel_forecast_flag_.[SumOfForecast Value USD]) AS [Total Of SumOfForecast Value USD]
FROM error_qry_funnel_forecast_flag_
GROUP BY error_qry_funnel_forecast_flag_.[Sales Rep Name], error_qry_funnel_forecast_flag_.[Forecast Flag]
PIVOT error_qry_funnel_forecast_flag_.[Financial Quarter];

Error Message:
The Microsoft Jet database engine does notrecognize '[Forms]![Direct Sales Dashboard]![Employee ID]' as a valid field name or expression.

Any help would be greatfully received.

Pete

 

[Remou]

You can use a parameter.

[tt]PARAMETERS Forms![Direct Sales Dashboard]![Employee ID] Long;
TRANSFORM ...

WHERE tbl_funnel_by_month_summary.[Employee ID]=Forms![Direct Sales Dashboard]![Employee ID]

GROUP BY ...
PIVOT ...[/tt]

 

[ps32208]

Many thanks for your help.

Do I need to add what you have put to the sql of the cross tab query?