Criteria in Crosstab 1

[kentwoodjean]

Have looked at multiple posts on this subject and tried several of the approaches and none seem to work so thought I would see if anyone can see the error. Below is my SQL:

TRANSFORM Last([IDW Monthly].Duration) AS [The Value]
SELECT [IDW Monthly].FirstName, [IDW Monthly].LastName, [IDW Monthly].Reason, Last([IDW Monthly].Duration) AS [Total Of Duration]
FROM [IDW Monthly]
WHERE ((([IDW Monthly].Team)=[Forms]![IWRTeamSelect]![IWRteam]))
GROUP BY [IDW Monthly].FirstName, [IDW Monthly].LastName, [IDW Monthly].Reason, [IDW Monthly].Team
ORDER BY [IDW Monthly].LastName
PIVOT [IDW Monthly].ToDate;

This is the criteria formula I used in my WHERE statement:

[Forms]![frmName]![combo box Name]

The error I get is "The MS jet database engine does not recognize '[Forms]![IWRTeamSelect]![IWRteam]'as a valid field name or expression.

What is wrong here?

 

[RoyVidar]

In query design view, enter the query meny, in the Parameter choice, specify the complete form reference, and correct datatype.

The SQL would then look something like this (should it be text):

[tt]PARAMETERS [Forms]![IWRTeamSelect]![IWRteam] Text ( 255 );
TRANSFORM Last([IDW Monthly].Duration) AS [The Value]...[/tt]

Roy-Vidar

 

[kentwoodjean]

That did it! Can I ask you this question please? I have not used the parameter choice from the menu box in the past for setting my parameters. Is this typcially used when setting parameters that point to a separate form for that purpose? Is there a reference that you could point me to for more information? I tried the Access help on line and that is not any good.

Thanks again.

 

[RoyVidar]

Me - I only use them when needed - that is, if the routine/query/report... goofs up;-) But I know ou need them for parameters from forms in crosstabs. I think also when using the query as source for a graph.

I also use them to when I need to specify parameters in code when opening the query. Somewhat down here thread701-993831 is an ADO sample. I don't think it is entirely necessary to declare the parameter in that situation, but I prefer to.

Roy-Vidar

 

[kentwoodjean]

Thanks for inlighting me. You always give the best advise and it is as easy to understand as it can be for persons like me who keep trying to learn.