Parameter Query stops working when used as sub-query

[jpopovaclark via AccessMonster.com]

Hello monsters,

Probably something stupidly obvious I'm doing wrong, but anyway (platform:
Access 2003 SP1):

I have a parameter query which works great. But when I go to use this
parameter query as a subquery to a summarising query I get:

"The Microsoft Jet database engine does not recognize '[After_When:]' as a
valid field name or expression."

However if I run the sub-query directly it brings up the parameter input box
as expected and then it runs...no problem.

here is the SQL of the query called "FPA Group Expenditure By Month" (this
runs fine):

SELECT IIf(IsNull([Reporting_Group]),"Not Allocated",[Reporting_Group]) AS
[GROUP], [FPA Expend/Commit].Source, [FPA Expend/Commit].FPA_GROUP, [FPA
Expend/Commit].FPA_AGREEMENT, [FPA Expend/Commit].Value, [FPA Expend/Commit].
GL_ACCOUNT, IIf(Left(Trim([GL_ACCOUNT]),1)="E",IIf(Mid(Trim([GL_ACCOUNT]),2,2)
="30","GAS",IIf(Mid(Trim([GL_ACCOUNT]),2,4)="2046","DIST",IIf(Mid(Trim(
[GL_ACCOUNT]),2,4)="2044","TRANS",IIf(Mid(Trim([GL_ACCOUNT]),2,4)="2045",
"CustConnct",IIf(Mid(Trim([GL_ACCOUNT]),2,2)="20","Other Reg","Other E"))))),
IIf(Left(Trim([GL_ACCOUNT]),1)="R","Retail",IIf(Left(Trim([GL_ACCOUNT]),1)
="S","Shrd Srvcsl",IIf(Len(Trim([GL_ACCOUNT]))=16 Or Len(Trim([GL_ACCOUNT]))
=19,"Other","Not Allocated")))) AS Unit_Department, IIf(Mid(Trim([GL_ACCOUNT])
,8,2)="22",IIf(Mid(Trim([GL_ACCOUNT]),12,3)="311","TRANS",IIf(Mid(Trim(
[GL_ACCOUNT]),12,3)="312","DIST","OTHER CAPEX")),IIf(Mid(Trim([GL_ACCOUNT]),8,
1)="7","OPEX",IIf(Len(Trim([GL_ACCOUNT]))=16 Or Len(Trim([GL_ACCOUNT]))=19,
"Other","Not Allocated"))) AS Program, Left(Trim([Date]),4) AS [Year], Mid
(Trim([Date]),5,2) AS [Month], Left(Trim([Date]),6) AS MonthYear
FROM [FPA Expend/Commit] LEFT JOIN [FPA groups] ON [FPA Expend/Commit].
FPA_GROUP = [FPA groups].FPA
WHERE ((([FPA Expend/Commit].Date)>[After_When:]));

Now here is the query that produces the error:

TRANSFORM Sum([FPA Group Expenditure By Month].Value) AS SumOfValue
SELECT [FPA Group Expenditure By Month].GROUP, [FPA Group Expenditure By
Month].Program
FROM [FPA Group Expenditure By Month]
GROUP BY [FPA Group Expenditure By Month].GROUP, [FPA Group Expenditure By
Month].Program
ORDER BY [FPA Group Expenditure By Month].GROUP, [FPA Group Expenditure By
Month].Program, [FPA Group Expenditure By Month].MonthYear
PIVOT [FPA Group Expenditure By Month].MonthYear;

I've tried changing the parameter label, but that doesn't seem to help.
Anybody know what's going on?

Thanks in advance,
Jeff
Brisbane, Australia

 

[jpopovaclark via AccessMonster.com]

Hello monsters again,

I solved my own problem. The issue was that my final query was a crosstab.
In order to have a crosstab query work with parameters you need to :

Declare the Parameters: In the DesignView of the Cross-
Tab, use the Menu Query / Parameters... to access the
Parameters Dialog where you can declare the Parameters and
their Types.

Hello monsters,

Probably something stupidly obvious I'm doing wrong, but anyway (platform:
Access 2003 SP1):

I have a parameter query which works great. But when I go to use this
parameter query as a subquery to a summarising query I get:

"The Microsoft Jet database engine does not recognize '[After_When:]' as a
valid field name or expression."

However if I run the sub-query directly it brings up the parameter input box
as expected and then it runs...no problem.