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;

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


"Karen" wrote:

> 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;
>

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.


"KARL DEWEY" wrote:

> 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
>
>
> "Karen" wrote:
>
> > 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;
> >