K
Karen
I have a query with a parameter from a form that runs just fine, but if I try
to base a cross-tab query it, I get an error that the Jet engine doesn't
recognize the parameter in the first query as being a valid field name or
exression. I can do a basic select query based on it without the error. If
I give a plain value, the cross-tab will run with no errors. The parameter
syntax should be fine since I've copied it from a nearly identical query
based on the same form.
Any idea what the problem/solution might be?
-Karen
The query with parameter as SQL is:
SELECT [Projects Billings Summarized by LCat query].PName, [Projects
Billings Summarized by LCat query].ProjectID, [Projects Billings Summarized
by LCat query].ActivePlan, [Projects Billings Summarized by LCat
query].Closed, [Projects Billings Summarized by LCat query].RDate, [Projects
Billings Summarized by LCat query].LCategory,
IIf(IsNull([aspendsum])=True,[fspendsum],[aspendsum]) AS LCatSpend,
IsNull([aspendsum]) AS NoAct
FROM [Projects Billings Summarized by LCat query] INNER JOIN Projects ON
([Projects Billings Summarized by LCat query].ActivePlan =
Projects.Activated) AND ([Projects Billings Summarized by LCat
query].ProjectID = Projects.ProjectID)
WHERE ((([Projects Billings Summarized by LCat
query].ProjectID)=[Forms]![QUERY TESTING]![ProjectIDPick]) AND (([Projects
Billings Summarized by LCat query].LCategory)<>"N/A"));
The simplest cross-tab as SQL (gives no error if a value is substituted for
the ProjectIDPick parameter):
TRANSFORM Sum([Program Review Export 4a Labor Cats].LCatSpend) AS
SumOfLCatSpend
SELECT [Program Review Export 4a Labor Cats].RDate
FROM [Program Review Export 4a Labor Cats]
GROUP BY [Program Review Export 4a Labor Cats].RDate
PIVOT [Program Review Export 4a Labor Cats].LCategory;
to base a cross-tab query it, I get an error that the Jet engine doesn't
recognize the parameter in the first query as being a valid field name or
exression. I can do a basic select query based on it without the error. If
I give a plain value, the cross-tab will run with no errors. The parameter
syntax should be fine since I've copied it from a nearly identical query
based on the same form.
Any idea what the problem/solution might be?
-Karen
The query with parameter as SQL is:
SELECT [Projects Billings Summarized by LCat query].PName, [Projects
Billings Summarized by LCat query].ProjectID, [Projects Billings Summarized
by LCat query].ActivePlan, [Projects Billings Summarized by LCat
query].Closed, [Projects Billings Summarized by LCat query].RDate, [Projects
Billings Summarized by LCat query].LCategory,
IIf(IsNull([aspendsum])=True,[fspendsum],[aspendsum]) AS LCatSpend,
IsNull([aspendsum]) AS NoAct
FROM [Projects Billings Summarized by LCat query] INNER JOIN Projects ON
([Projects Billings Summarized by LCat query].ActivePlan =
Projects.Activated) AND ([Projects Billings Summarized by LCat
query].ProjectID = Projects.ProjectID)
WHERE ((([Projects Billings Summarized by LCat
query].ProjectID)=[Forms]![QUERY TESTING]![ProjectIDPick]) AND (([Projects
Billings Summarized by LCat query].LCategory)<>"N/A"));
The simplest cross-tab as SQL (gives no error if a value is substituted for
the ProjectIDPick parameter):
TRANSFORM Sum([Program Review Export 4a Labor Cats].LCatSpend) AS
SumOfLCatSpend
SELECT [Program Review Export 4a Labor Cats].RDate
FROM [Program Review Export 4a Labor Cats]
GROUP BY [Program Review Export 4a Labor Cats].RDate
PIVOT [Program Review Export 4a Labor Cats].LCategory;