changing parameters for query

[MICKI0220]

I have created a form where a user fills in several boxes as parameter criteria in the query that populates a report. Currently the criteria in the query has a between statement, giving the user the information for the jobs that are in that range. The user wants to be able to select, lets say, five jobs instead of getting all the jobs within the range. How do I code that.

INSERT INTO PayrollHisTable ( EmployeeId, EarningCode, CostCode, JobNo, cPayRate_amt, Hours, LastName, FirstName, Middle )
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 
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.dtWork_dt 
HAVING (((dbo_EmpPayDetailHist.szEmployeeId_tr) Between [Forms]![frmPayrollHistory]![txtEmployeeFrom] And [Forms]![frmPayrollHistory]![txtEmployeeTo:]) AND ((dbo_JobCostCode.szSearch_key) Like [Forms]![frmPayrollHistory]![txtCostCode]) AND ((dbo_JobSearch.szSearch_key) Like [Forms]![frmPayrollHistory]![txtJobNoFrom]) AND ((dbo_EmpPayDetailHist.dtWork_dt) Between [Forms]![frmPayrollHistory]![txtStart] And [Forms]![frmPayrollHistory]![txtEnd]));

 

[Remou]

You could do this with a listbox that allowed the users to select the jobs in which they are interested, however, you will need to build your sql in code. I haved used aliases for the table names, eg FROM ((dbo_EmpPayDetailHist epdh, where epdh is the alias. Here are some notes.

''The row source for the lisr box might be:
''SELECT JobNo, JobDescription FROM JobsTable

For Each itm In Me.JobsListBox.ItemsSelected
  listofvalues = listofvalues & "," & Me.JobsListBox.Column(0, itm)
Next

listofvalues = Mid(listofvalues, 2)

intEmpFrom = [Forms]![frmPayrollHistory]![txtEmployeeFrom]
intEmpTo = [Forms]![frmPayrollHistory]![txtEmployeeTo:]
sCostCode = [Forms]![frmPayrollHistory]![txtCostCode]
sJobNoFrom = [Forms]![frmPayrollHistory]![txtJobNoFrom]
sStartDate = "#" & Format([Forms]![frmPayrollHistory]![txtStart], "yyyy/mm/dd") & "#"
sEndDate = "#" & Format([Forms]![frmPayrollHistory]![txtEnd], "yyyy/mm/dd") & "#"

''Current SQL
sSQL = "INSERT INTO PayrollHisTable ( EmployeeId, EarningCode, CostCode, JobNo, cPayRate_amt, "
sSQL = sSQL & "LastName, FirstName, Middle, Hours )"
sSQL = sSQL & "SELECT   epdh.szEmployeeId_tr, epdh.szEarnCode_tr, jcc.szSearch_key, js.szSearch_key, epdh.cPayRate_amt, "
sSQL = sSQL & "pegi.LastName, pegi.FirstName, pegi.MiddleInit, Sum(epdh.dHoursWorked_qty)"
sSQL = sSQL & "FROM ((dbo_EmpPayDetailHist epdh"
sSQL = sSQL & " JOIN dbo_JobCostCode jcc ON epdh.lJobCostCode_id = jcc.lJobCostCode_id) "
sSQL = sSQL & "INNER JOIN dbo_JobSearch js ON epdh.lJob_id = js.lJobSearch_id) "
sSQL = sSQL & "LEFT JOIN dbo_tblPaEmpGenInfo pegi ON epdh.szEmployeeId_tr = pegi.EmployeeId "

sSQL = sSQL & "GROUP BY epdh.szEmployeeId_tr, epdh.szEarnCode_tr, jcc.szSearch_key, js.szSearch_key, epdh.cPayRate_amt, "
sSQL = sSQL & "pegi.LastName, pegi.FirstName, pegi.MiddleInit, epdh.dtWork_dt "

sSQL = sSQL & "HAVING (((epdh.szEmployeeId_tr) Between " & intEmpFrom & " And " & intEmpTo & ") "
sSQL = sSQL & "AND ((jcc.szSearch_key) Like " & sCostCode & ") "
''So I guess this is the line that must change
''sSQL = sSQL & "AND ((js.szSearch_key) Like " & sJobNoFrom & ") "
sSQL = sSQL & "AND ((js.szSearch_key) IN (" & listofvalues & ")) "
sSQL = sSQL & "AND ((epdh.dtWork_dt) Between " & sStartDate & " And " & sEndDate & "));"

http://lessthandot.com