I have a report based in a crosstab query. it has 2 parameters pass from a form.
My problem is that if I use the form to pass the parmeters the report misses fiels that has 0 values.
If I use user input parameters the report works fine it shows fields that has
0 value.
Here is the sql for both ways the query is run.
“This is the code for the query with parameters user input:
PARAMETERS [job] Short;
TRANSFORM Sum([Data Payroll Hours].Hours_Expended) AS [SumOfHours Expended]
SELECT [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num, [Data Tasks].Change_Order, [Data Tasks].Modified, [Data Tasks].Task_Name, [Data Tasks].Floor, [Data Tasks].Hours, [Data Tasks].Complete, [Data Job Information].Job_Name, [Data Tasks].Area, Sum([Data Payroll Hours].Hours_Expended) AS [Total of Hours Expended]
FROM ([Data Job Information] INNER JOIN [Data Tasks] ON [Data Job Information].Job_Number = [Data Tasks].Job_num) LEFT JOIN [Data Payroll Hours] ON ([Data Tasks].Job_num = [Data Payroll Hours].Job_num) AND ([Data Tasks].Trade = [Data Payroll Hours].Trade) AND ([Data Tasks].Task_num = [Data Payroll Hours].Task_num)
WHERE ((([Data Job Information].Job_Number)=[job]))
GROUP BY [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num, [Data Tasks].Change_Order, [Data Tasks].Modified, [Data Tasks].Task_Name, [Data Tasks].Floor, [Data Tasks].Hours, [Data Tasks].Complete, [Data Job Information].Job_Name, [Data Tasks].Area, [Data Tasks].Percent_Complete
ORDER BY [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num
PIVOT [Data Payroll Hours].Emp_num;
This is with Form pass parameters:
PARAMETERS [Forms]![Form3]![Text2] Short, [forms]![form3]![text10] DateTime;
TRANSFORM Sum([Data Payroll Hours].Hours_Expended) AS [SumOfHours Expended]
SELECT [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num, [Data Tasks].Change_Order, [Data Tasks].Modified, [Data Tasks].Task_Name, [Data Tasks].Floor, [Data Tasks].Hours, [Data Tasks].Complete, [Data Job Information].Job_Name, [Data Tasks].Area, Sum([Data Payroll Hours].Hours_Expended) AS [Total of Hours Expended]
FROM ([Data Job Information] INNER JOIN [Data Tasks] ON [Data Job Information].Job_Number = [Data Tasks].Job_num) LEFT JOIN [Data Payroll Hours] ON ([Data Tasks].Job_num = [Data Payroll Hours].Job_num) AND ([Data Tasks].Trade = [Data Payroll Hours].Trade) AND ([Data Tasks].Task_num = [Data Payroll Hours].Task_num)
WHERE ((([Data Payroll Hours].Field1)<=[Forms]![Form3]![Text10]) AND (([Data Job Information].Job_Number)=[Forms]![Form3]![Text2]))
GROUP BY [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num, [Data Tasks].Change_Order, [Data Tasks].Modified, [Data Tasks].Task_Name, [Data Tasks].Floor, [Data Tasks].Hours, [Data Tasks].Complete, [Data Job Information].Job_Name, [Data Tasks].Area, [Data Tasks].Percent_Complete
ORDER BY [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num
PIVOT [Data Payroll Hours].Emp_num;
My problem is that if I use the form to pass the parmeters the report misses fiels that has 0 values.
If I use user input parameters the report works fine it shows fields that has
0 value.
Here is the sql for both ways the query is run.
“This is the code for the query with parameters user input:
PARAMETERS [job] Short;
TRANSFORM Sum([Data Payroll Hours].Hours_Expended) AS [SumOfHours Expended]
SELECT [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num, [Data Tasks].Change_Order, [Data Tasks].Modified, [Data Tasks].Task_Name, [Data Tasks].Floor, [Data Tasks].Hours, [Data Tasks].Complete, [Data Job Information].Job_Name, [Data Tasks].Area, Sum([Data Payroll Hours].Hours_Expended) AS [Total of Hours Expended]
FROM ([Data Job Information] INNER JOIN [Data Tasks] ON [Data Job Information].Job_Number = [Data Tasks].Job_num) LEFT JOIN [Data Payroll Hours] ON ([Data Tasks].Job_num = [Data Payroll Hours].Job_num) AND ([Data Tasks].Trade = [Data Payroll Hours].Trade) AND ([Data Tasks].Task_num = [Data Payroll Hours].Task_num)
WHERE ((([Data Job Information].Job_Number)=[job]))
GROUP BY [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num, [Data Tasks].Change_Order, [Data Tasks].Modified, [Data Tasks].Task_Name, [Data Tasks].Floor, [Data Tasks].Hours, [Data Tasks].Complete, [Data Job Information].Job_Name, [Data Tasks].Area, [Data Tasks].Percent_Complete
ORDER BY [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num
PIVOT [Data Payroll Hours].Emp_num;
This is with Form pass parameters:
PARAMETERS [Forms]![Form3]![Text2] Short, [forms]![form3]![text10] DateTime;
TRANSFORM Sum([Data Payroll Hours].Hours_Expended) AS [SumOfHours Expended]
SELECT [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num, [Data Tasks].Change_Order, [Data Tasks].Modified, [Data Tasks].Task_Name, [Data Tasks].Floor, [Data Tasks].Hours, [Data Tasks].Complete, [Data Job Information].Job_Name, [Data Tasks].Area, Sum([Data Payroll Hours].Hours_Expended) AS [Total of Hours Expended]
FROM ([Data Job Information] INNER JOIN [Data Tasks] ON [Data Job Information].Job_Number = [Data Tasks].Job_num) LEFT JOIN [Data Payroll Hours] ON ([Data Tasks].Job_num = [Data Payroll Hours].Job_num) AND ([Data Tasks].Trade = [Data Payroll Hours].Trade) AND ([Data Tasks].Task_num = [Data Payroll Hours].Task_num)
WHERE ((([Data Payroll Hours].Field1)<=[Forms]![Form3]![Text10]) AND (([Data Job Information].Job_Number)=[Forms]![Form3]![Text2]))
GROUP BY [Data Tasks].Job_num, [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num, [Data Tasks].Change_Order, [Data Tasks].Modified, [Data Tasks].Task_Name, [Data Tasks].Floor, [Data Tasks].Hours, [Data Tasks].Complete, [Data Job Information].Job_Name, [Data Tasks].Area, [Data Tasks].Percent_Complete
ORDER BY [Data Tasks].Trade, [Data Tasks].Phase, [Data Tasks].Task_num
PIVOT [Data Payroll Hours].Emp_num;
