Hello all ,
I recently create some crosstab querys and now i need to implement WHERE Clauses or you can say some paremeters and basically this would be a search query .
First of all this is the first time that i am trying to use parameters to crosstab and as i read , i need to define parameters throught the query parameters and i am not sure even if i made it correct.
Here is my query:
PARAMETERS [Forms]![Form1]![Year] Value;
TRANSFORM Nz(Sum(Main.Duration))+0 AS SumOfDuration
FROM Season INNER JOIN (LeaveType INNER JOIN (Employee RIGHT JOIN Main ON Employee.EmployeeID = Main.EmployeeID) ON LeaveType.LeaveID = Main.LeaveID) ON Season.SeasonID = Main.SeasonID
WHERE (((Season.Aseason) Like [Forms]![Form1]![Year] & "*")) OR ((([Forms]![Form1]![Year]) Is Null))
GROUP BY Employee.EmployeeName
(((Season.Aseason) Like [Forms]![Form1]![Year] & "*")) OR ((([Forms]![Form1]![Year]) Is Null))
The Season.Aseason .. etc .. basiccaly is the parameters that i want to use so i am not sure if the PARAMETERS that i have wrote is correct .
Anyway, when i am in the query and i run it the resutls are correct.
Then i create a report based on my query and when i open it , it ask me the parameters and i am inserting for example 2012 , but it keeps asking me again its like a nested .
When i am writing to it 2013 it produces an error and it says : .. Does not recognize " as a valid field name or expression .
My first thought its that the query is not correct and the second if i need to change some options from the report properties .
Also the user will fill the query parameters throught a form as you can see [Forms]![Form1]![Year] so there would not be any popup/parameter window .
The parameters should probably be a single variable name that gets set when the query is called. I would use Parameters theYr Value; (double check the syntax as I am assuming yours is correct). When I run the query is run, you can open it on your form, and assign the year at that time. Another way is to put the parameter into a table and do an inner join. Your method might work, but I would put it into a sub query first. Then use that query to populate your cross tab query. I have found that this will greatly speed up your query. -- John M Reynolds