Link to home
Start Free TrialLog in
Avatar of AAI_Development
AAI_Development

asked on

Parameter in Query (SQL Server Report Builder) not working?

I'm running a rather simple query with a parameter, so that in a report, the user can enter the year and a chart will display data from said year.
If I hard-code in the year, like this:

SELECT 
"All Parts" AS `Product Part Number`, 
Month(`DateReworked`) AS Mo, 
Year(`Datereworked`) AS Yr,
Avg(tblReworkRecords.DaysToRework) AS AvgOfElapsedDays

FROM 
(`Product part number List` 
INNER JOIN `Shop Order List` 
ON `Product part number List`.`Product Part Number` = `Shop Order List`.`Product Part Number`) 
INNER JOIN tblReworkRecords 
ON `Shop Order List`.`Shop Order Number` = tblReworkRecords.ShopOrderNumber

WHERE 
((Year(tblReworkRecords.Datereworked) = 2010)) 
AND 
(((tblReworkRecords.DateReworked)>'1/1/2004'))

GROUP BY Month(`DateReworked`)

HAVING (((Avg(tblReworkRecords.DaysToRework)) Is Not Null))

Order BY `Product Part Number`, Mo, AvgOfElapsedDays

Open in new window


I get exactly what I am looking for for 2010 (12 rows, 1 for each month)

However if I used the EXACT same code, only where it says ((Year(tblReworkRecords.Datereworked) = 2010)), I change it to ((Year(tblReworkRecords.Datereworked) = @Year)) , and run the query (it prompts me to enter a year for the parameter, and I type in 2010), I get absolutely no rows of data, none at all.

Does anyone know what I am doing wrong or why I am getting no data?

Thank you in advance.
Avatar of robertg34
robertg34
Flag of United States of America image

Double check your parameters and make sure @Year is mapped correctly to that input box.  Also make sure the parameter is data-typed correctly.  I'm assuming its a number field.  

It might be helpful to put a default value in the parameter as well.  
Avatar of AAI_Development
AAI_Development

ASKER

Tried every data-type, tried a default value....nothing...
ASKER CERTIFIED SOLUTION
Avatar of AAI_Development
AAI_Development

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Was correct, but I have non-compatable data source.