query that uses multiselect from form 1

[MICKI0220]

I have a form that I need the user to be able to choose what ever they want to fill in to manipulate the query. Currently I can only get a few combinations to work. All combinations require the date range and the employee id range. Currently I can only get the combination of the jobNo to work alone. If I combine it with the cost code or dept I get all the job no's instead of the single job I type in. Data would look like this

emloyee ID.....CostCode.....JobNO......Dept......date...

20470..........FC10.........I-7301.....Field.....9/8/2012
30142..........FC30.........E-7617......SUB......9/8/2012
30125..........BC10.........E-5475......Truck....9/7/2012
14253..........BC10.........I-7317......Truck....9/8/2012
25862..........FC30.........I-7301......SUB......9/7/2012

The code I have been trying to get to work is below.

INSERT INTO PayrollHisTable ( EmployeeId, EarningCode, CostCode, JobNo, cPayRate_amt, Hours, LastName, FirstName, Middle, Dept, [Date] )
SELECT dbo_EmpPayDetailHist.szEmployeeId_tr AS EmployeeId, dbo_EmpPayDetailHist.szEarnCode_tr AS EarningCode, dbo_JobCostCode.szSearch_key AS CostCode, dbo_JobSearch.szSearch_key AS JobNo, dbo_EmpPayDetailHist.cPayRate_amt, Sum(dbo_EmpPayDetailHist.dHoursWorked_qty) AS SumOfdHoursWorked_qty, dbo_tblPaEmpGenInfo.LastName, dbo_tblPaEmpGenInfo.FirstName, dbo_tblPaEmpGenInfo.MiddleInit, dbo_EmpPayDetailHist.szDepartmentId_tr, dbo_EmpPayDetailHist.dtWork_dt
FROM ((dbo_EmpPayDetailHist INNER JOIN dbo_JobCostCode ON dbo_EmpPayDetailHist.lJobCostCode_id = dbo_JobCostCode.lJobCostCode_id) INNER JOIN dbo_JobSearch ON dbo_EmpPayDetailHist.lJob_id = dbo_JobSearch.lJobSearch_id) LEFT JOIN dbo_tblPaEmpGenInfo ON dbo_EmpPayDetailHist.szEmployeeId_tr = dbo_tblPaEmpGenInfo.EmployeeId
GROUP BY dbo_EmpPayDetailHist.szEmployeeId_tr, dbo_EmpPayDetailHist.szEarnCode_tr, dbo_JobCostCode.szSearch_key, dbo_JobSearch.szSearch_key, dbo_EmpPayDetailHist.cPayRate_amt, dbo_tblPaEmpGenInfo.LastName, dbo_tblPaEmpGenInfo.FirstName, dbo_tblPaEmpGenInfo.MiddleInit, dbo_EmpPayDetailHist.szDepartmentId_tr, dbo_EmpPayDetailHist.dtWork_dt
HAVING (((dbo_EmpPayDetailHist.szEmployeeId_tr) Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:]) AND ((dbo_JobSearch.szSearch_key) Between [Forms]![frmPayrollHistory]![txtJobNoFrom] And [Forms]![frmPayrollHistory]![txtJobNoTo]) AND ((dbo_EmpPayDetailHist.dtWork_dt) Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd])) OR (((dbo_EmpPayDetailHist.szEmployeeId_tr) Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:]) AND ((dbo_JobCostCode.szSearch_key) Between [Forms]![frmPayrollHistory]![txtCostCode] And [Forms]![frmPayrollHistory]![txtCostCodeto]) AND ((dbo_JobSearch.szSearch_key) Between [Forms]![frmPayrollHistory]![txtJobNoFrom] And [Forms]![frmPayrollHistory]![txtJobNoTo]) AND ((dbo_EmpPayDetailHist.dtWork_dt) Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd])) OR (((dbo_EmpPayDetailHist.szEmployeeId_tr) Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:]) AND ((dbo_JobSearch.szSearch_key) Between [Forms]![frmPayrollHistory]![txtJobNoFrom] And [Forms]![frmPayrollHistory]![txtJobNoTo]) AND ((dbo_EmpPayDetailHist.szDepartmentId_tr) Between [Forms]![frmPayrollHistory]![txtdeptfrom] And [Forms]![frmPayrollHistory]![txtdeptto]) AND ((dbo_EmpPayDetailHist.dtWork_dt) Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd])) OR (((dbo_EmpPayDetailHist.szEmployeeId_tr) Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:]) AND ((dbo_JobCostCode.szSearch_key) Between [Forms]![frmPayrollHistory]![txtCostCode] And [Forms]![frmPayrollHistory]![txtCostCodeto]) AND ((dbo_EmpPayDetailHist.dtWork_dt) Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd])) OR (((dbo_EmpPayDetailHist.szEmployeeId_tr) Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:]) AND ((dbo_JobCostCode.szSearch_key) Between [Forms]![frmPayrollHistory]![txtCostCode] And [Forms]![frmPayrollHistory]![txtCostCodeto]) AND ((dbo_EmpPayDetailHist.szDepartmentId_tr) Between [Forms]![frmPayrollHistory]![txtdeptfrom] And [Forms]![frmPayrollHistory]![txtdeptto]) AND ((dbo_EmpPayDetailHist.dtWork_dt) Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd])) OR (((dbo_EmpPayDetailHist.szEmployeeId_tr) Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:]) AND ((dbo_EmpPayDetailHist.szDepartmentId_tr) Between [Forms]![frmPayrollHistory]![txtdeptfrom] And [Forms]![frmPayrollHistory]![txtdeptto]) AND ((dbo_EmpPayDetailHist.dtWork_dt) Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd])) OR (((dbo_EmpPayDetailHist.szEmployeeId_tr) Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:]) AND ((dbo_JobCostCode.szSearch_key) Between [Forms]![frmPayrollHistory]![txtCostCode] And [Forms]![frmPayrollHistory]![txtCostCodeto]) AND ((dbo_JobSearch.szSearch_key) Between [Forms]![frmPayrollHistory]![txtJobNoFrom] And [Forms]![frmPayrollHistory]![txtJobNoTo]) AND ((dbo_EmpPayDetailHist.szDepartmentId_tr) Between [Forms]![frmPayrollHistory]![txtdeptfrom] And [Forms]![frmPayrollHistory]![txtdeptto]) AND ((dbo_EmpPayDetailHist.dtWork_dt) Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd]));

Thank you for any assistance with this.

Micki

 

[dhookom]

If you want the returned records to match all the criteria, why have all the OR in the query?

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

I only want the criteria to be what the user populates. For example there are five areas for them to enter data. 2 areas(employee id range and date range) need to be populated. The others areas, they can choose to limit their report furter by choosing , one, two or all three extra areas.

 

[dhookom]

I would use code to loop through the controls to see if the user has entered anything. Use this code to build an SQL statement to use as the record source of the form.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT H.szEmployeeId_tr AS EmployeeId, H.szEarnCode_tr AS EarningCode, C.szSearch_key AS CostCode, S.szSearch_key AS JobNo, H.cPayRate_amt
, Sum(H.dHoursWorked_qty) AS SumOfdHoursWorked_qty, I.LastName, I.FirstName, I.MiddleInit, H.szDepartmentId_tr, H.dtWork_dt
  FROM ((dbo_EmpPayDetailHist H
 INNER JOIN dbo_JobCostCode C ON H.lJobCostCode_id = C.lJobCostCode_id) 
 INNER JOIN dbo_JobSearch S ON H.lJob_id = S.lJobSearch_id) 
  LEFT JOIN dbo_tblPaEmpGenInfo I ON H.szEmployeeId_tr = I.EmployeeId
 WHERE (H.szEmployeeId_tr Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:])
   AND (H.dtWork_dt Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd])
   AND (S.szSearch_key Between Nz([Forms]![frmPayrollHistory]![txtJobNoFrom],S.szSearch_key) And Nz([Forms]![frmPayrollHistory]![txtJobNoTo],S.szSearch_key))
   AND (C.szSearch_key Between Nz([Forms]![frmPayrollHistory]![txtCostCode],C.szSearch_key) And Nz([Forms]![frmPayrollHistory]![txtCostCodeto],C.szSearch_key))
   AND (H.szDepartmentId_tr Between Nz([Forms]![frmPayrollHistory]![txtdeptfrom],H.szDepartmentId_tr) And Nz([Forms]![frmPayrollHistory]![txtdeptto],H.szDepartmentId_tr))
 GROUP BY H.szEmployeeId_tr, H.szEarnCode_tr, C.szSearch_key, S.szSearch_key, H.cPayRate_amt
, I.LastName, I.FirstName, I.MiddleInit, H.szDepartmentId_tr, H.dtWork_dt

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

Thank you PHV. I will try it out.

 

[MICKI0220]

PHV. That worked great for what I was asking for. I have run into another quandry relating to the same query. If my table that I am querying on has a null field for one of the values I have put in the such as (C.szSearch_key Between Nz([Forms]![frmPayrollHistory]![txtCostCode]. Then my results is nothing. How can I check for this and bypass it. Basically I am appending data to a table. If the user puts data in the txtcostcode field, then this table would not have this field populated and I wouldn't want it to come up with any values. However if I put this code in there it stops it from getting data when it does need some data. Of the 5 criteria fields it has data in Employee id, date and department. the JobNo field and the Costcode field are blank. My user would only want the data of this table to append to the main one, if the department field is what she is requesting. If the Phase is typed in she does not want this data. Basically if I put the nz code in the criteria for phase, it doesn't get any data when I put in the right department. But if I leave that criteria out, and the user just populates the date, employee id and the Phase, it gives her all the data in that table that meets the employee id and the date criteria. I hope I make sense. I just need it to look at the null field and see that it doesn't match the entry she typed in and not come up with any records.