Calculations criteria to be placed from control

[stretch66]

Have a staff report that counts all staff and breakdown of job type and job status including percentages but I want to place a criteria on from a control on a form. Have placed it against Department name which then displays just those staff in the department but cannot get it to work against the calculations as says string is too long.

The query is :
SELECT [First_Name] & " " & [Last_Name] AS name, tblEmployees.EmployeeID, tblJobStatus.JobStatusNumber, tblDepartment.DepartmentID, tblDepartment.DepartmentName, tblEmployees.StartDate, tblJob.Position, tblJob.Grade, tblEmployees.Payroll_Number, DCount("[EmployeeID]","tblEmployees","[job_status]=1") AS TotalPerm, DCount("[EmployeeID]","tblEmployees","[job_status]=2") AS TotalTemp, DCount("[EmployeeID]","tblEmployees","[job_status]=3") AS TotalProb, DCount("[EmployeeID]","tblEmployees","[jobtype]=1") AS TotalCasual, DCount("[EmployeeID]","tblEmployees","[jobtype]=2") AS TotalPT, DCount("[EmployeeID]","tblEmployees","[jobtype]=3") AS TotalContract, DCount("[EmployeeID]","tblEmployees","[jobtype]=4") AS TotalFT, Sum(DCount("[EmployeeID]","tblEmployees","[job_status]=1")+(DCount("[EmployeeID]","tblEmployees","[job_status]=2"))+(DCount("[EmployeeID]","tblEmployees","[job_status]=3"))) AS GrandTotal, tblJobType.JobTypeID
FROM tblJobType INNER JOIN ((tblDepartment INNER JOIN (tblEmployees INNER JOIN tblJobStatus ON tblEmployees.Job_Status = tblJobStatus.JobStatusNumber) ON tblDepartment.DepartmentID = tblEmployees.DepartmentID) INNER JOIN tblJob ON tblEmployees.JobID = tblJob.JobID) ON tblJobType.JobTypeID = tblEmployees.JobType
GROUP BY [First_Name] & " " & [Last_Name], tblEmployees.EmployeeID, tblJobStatus.JobStatusNumber, tblDepartment.DepartmentID, tblDepartment.DepartmentName, tblEmployees.StartDate, tblJob.Position, tblJob.Grade, tblEmployees.Payroll_Number, DCount("[EmployeeID]","tblEmployees","[job_status]=1"), DCount("[EmployeeID]","tblEmployees","[job_status]=2"), DCount("[EmployeeID]","tblEmployees","[job_status]=3"), tblJobType.JobTypeID
HAVING (((tblDepartment.DepartmentName)=[Forms]![fmSel_DeptStaff]![cboDeptStaff]))
ORDER BY [First_Name] & " " & [Last_Name], tblDepartment.DepartmentName, tblJob.Position, tblJob.Grade;


The control is :[Forms]![fmSel_DeptStaff]![cboDeptStaff]

Could someone please tell me where I'm going wrong.

 

[GUIDO22]

Can you explain further - you are attempting to place this code where exactly?

NB. Access is quite right - this is a very long query string!

 

[stretch66]

Actually split it up with a sub form and worked in the end.