CRITERIA FOR CROSSTAB QUERIES 1

[villica]

I have a crosstab query but I need to get the crieteria for the query from a form.
for example state = forms!location!state. It gives me an error. What is the proper way. Can a crostab query have parameters. Any help suggestions would be appreciated. Thank you all. I have always got lots of help on this forum.

 

[freewilly]

Try [forms].[location].[state]
The form has to be open for this to work

 

[LarryDeLaruelle]

Check how you have this field defined in the query.

If you are using State just for a selection criteria, then "Where" should be selected in the Total line.
Larry De Laruelle
larry1de@yahoo.com

 

[villica]

Hello freewilly, I tried the above code but it does not work. I get the following error
the microsoft database engine does not recognize [forms].[location].[state]. I also tried forms!location!state. Any other suggetions. It almost make me think that you can't add a criteria to a crosstab query, can this be possible. Can anyone please tell me

 

[HBT]

villica:

Did you ever find a resolution to this problem? I am exeperiencing the same issue.

 

[MichaelJ]

Try this. Go to the SGL view of your query. Insert the top line as shown here above the TRANSFORM statement. The key is to list the parameter as it is listed in your criteria line in the query grid and then specify the data type in the parameters statement. See my example below. Let me know if you have any difficulty with this.

Have fun!

Mike


PARAMETERS [Start date] DateTime, [End date] DateTime;
TRANSFORM Avg(qryTargetCompareGraph.WeightedMaturity) AS [The Value]
SELECT qryTargetCompareGraph.UnitArea, qryTargetCompareGraph.AssessmentDate
FROM qryTargetCompareGraph
WHERE (((qryTargetCompareGraph.AssessmentDate) Between [start date] And [end date]))
GROUP BY qryTargetCompareGraph.UnitArea, qryTargetCompareGraph.AvgOfProcessMaturity, qryTargetCompareGraph.AssessmentDate
PIVOT qryTargetCompareGraph.Description;