Crosstab Query Input Failure

[Karen]

I have a Crosstab query that gives me the summarized total per month
(columns) for each employee (row) on a project. I have a 'Where' criteria to
limit the results to a single project. While I was working out the Crosstab,
I had just put a fixed project ID number in and everything worked fine. Once
done I substituted a control on the project form where I'd like to call the
Crosstab from, but now the Crosstab fails and gives me an error that the 'Jet
database engine doesn't recognize
'[Forms]![Project_Plan_Detailed_Form]![ProjectID]' as a valid field name or
expression'.

Can anyone tell me why this has happened or how to solve it? The query this
calls ("Resource Plan Conditioned Spending") is used in several other places
and can't be modified to suit just this Crosstab.

The SQLView of the query is:
TRANSFORM Sum([Resource Plan Conditioned Spending].Forecast) AS SumOfForecast
SELECT [Resource Plan Conditioned Spending].RType, [Resource Plan
Conditioned Spending].ResName, [Resource Plan Conditioned
Spending].ResShortName
FROM [Resource Plan Conditioned Spending]
WHERE ((([Resource Plan Conditioned
Spending].ProjectID)=[Forms]![Project_Plan_Detailed_Form]![ProjectID]))
GROUP BY [Resource Plan Conditioned Spending].RType, [Resource Plan
Conditioned Spending].ResName, [Resource Plan Conditioned
Spending].ResShortName
ORDER BY [Resource Plan Conditioned Spending].RDate
PIVOT [Resource Plan Conditioned Spending].RDate;

 

[KARL DEWEY]

Parameters must be declaried in a crosstab query.
Open the crosstab query in design view and click on menu QUERY - Parameters.
Paste [Forms]![Project_Plan_Detailed_Form]![ProjectID] in the
Paremeter field and in the Data Type enter TEXT.
Make sure the paste is an exact copy of the parameter used elsewhere in the
queries.

 

[Karen]

Thanks! I did have to play with it a little since at first it gave me a new
error even though I checked that everything was identical ('The expression is
typed incorrectly, or it is too complex to be evaluated...' ). Turned out
the only issue was that it's not a textual ID, it's numeric. Once I changed
the parameter type from 'text' to 'value' ('decimal' didn't work) it worked
smoothly.

Parameters must be declaried in a crosstab query.
Open the crosstab query in design view and click on menu QUERY - Parameters.
Paste [Forms]![Project_Plan_Detailed_Form]![ProjectID] in the
Paremeter field and in the Data Type enter TEXT.
Make sure the paste is an exact copy of the parameter used elsewhere in the
queries.
--
KARL DEWEY
Build a little - Test a little

I have a Crosstab query that gives me the summarized total per month
(columns) for each employee (row) on a project. I have a 'Where' criteria to
limit the results to a single project. While I was working out the Crosstab,
I had just put a fixed project ID number in and everything worked fine. Once
done I substituted a control on the project form where I'd like to call the
Crosstab from, but now the Crosstab fails and gives me an error that the 'Jet
database engine doesn't recognize
'[Forms]![Project_Plan_Detailed_Form]![ProjectID]' as a valid field name or
expression'.

Can anyone tell me why this has happened or how to solve it? The query this
calls ("Resource Plan Conditioned Spending") is used in several other places
and can't be modified to suit just this Crosstab.

The SQLView of the query is:
TRANSFORM Sum([Resource Plan Conditioned Spending].Forecast) AS SumOfForecast
SELECT [Resource Plan Conditioned Spending].RType, [Resource Plan
Conditioned Spending].ResName, [Resource Plan Conditioned
Spending].ResShortName
FROM [Resource Plan Conditioned Spending]
WHERE ((([Resource Plan Conditioned
Spending].ProjectID)=[Forms]![Project_Plan_Detailed_Form]![ProjectID]))
GROUP BY [Resource Plan Conditioned Spending].RType, [Resource Plan
Conditioned Spending].ResName, [Resource Plan Conditioned
Spending].ResShortName
ORDER BY [Resource Plan Conditioned Spending].RDate
PIVOT [Resource Plan Conditioned Spending].RDate;