building querry from multi select linkbox to produce report

[northernbeaver]

I have a form with a multi select listbox that lists employee (2 columns-first is empid second is employee name). I need to build a query from the listbox itemselected field so just the selected employees are shown in the report. any ideas how to start?

 

[jfgambit]

You want to pass the IN() criteria to the query...here is an example:

strSQL = "SELECT tblSales.RptEmpID AS MngrID, IIf(IsNull([tblSales_1]![ComName]),[tblSales]![ComName],[tblSales_1]![ComName]) AS MngrName, tblPOLog.EmpID, tblSales.ComName, tblSales.SalesArea, tblSales.EAddr, tblPOLog.DateRec, tblQuarters.QtrMonth, tblQuarters.QtrYr, tblPOLog.BillCust, tblPOLog.ShipCust, tblPOLog.PONbr, tblPOLog.POAmt, tblPOLog.POCat, tblPOLog.RsnCode, tblRsnCodes.RsnDesc, tblPOLog.DateBooked, IIf(IsNull([DateReject2]),[DateReject],[DateReject2]) AS RejectDate " & _
"FROM (((tblPOLog LEFT JOIN tblSales ON tblPOLog.EmpID = tblSales.EmpID) LEFT JOIN tblRsnCodes ON tblPOLog.RsnCode = tblRsnCodes.RsnCode) LEFT JOIN tblQuarters ON tblPOLog.DateRec = tblQuarters.QtrDate) LEFT JOIN tblSales AS tblSales_1 ON tblSales.RptEmpID = tblSales_1.EmpID "

strWhere = "WHERE (((tblPOLog.POCat)='R') AND ((tblPOLog.RsnCode)<>'DUP') AND ((tblPOLog.DateBooked) Is Null) AND ((tblPOLog.Closed)=No)) AND"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtlstMngr) Then
strWhere = strWhere & "(tblSales.RptEmpID) In (" & Me.txtlstMngr & ") AND"
End If

If Not IsNull(Me.txtlstEmp) Then
strWhere = strWhere & "(tblPOLog.EmpID) In(" & Me.txtlstEmp & ") AND"
End If

If Not IsNull(Me.txtlstReason) Then
strWhere = strWhere & "(tblPOLog.RsnCode) In(" & Me.txtlstReason & ") AND"
End If

If Not IsNull(Me.txtlstArea) Then
strWhere = strWhere & "(tblSales.SalesArea) In(" & Me.txtlstArea & ") AND"
End If

'Set the WHERE clause for the Date Range Textboxes if information has been entered
If Not IsNull(Me.txtSDate) Then
strWhere = strWhere & "(tblPOLog.DateRec) Between #" & Me.txtSDate & "# AND #" & Me.txtEDate & "# AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryRejectedOrdersNotBooked")
qryDef.SQL = strSQL & " " & strWhere '& " " & strOrder
DoCmd.OpenReport "rptRejectedOrdersNotBooked", acViewPreview


&quot;I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?&quot;

 

[RoyVidar]

Another suggestion could be having a look here thread702-787778, where the wherecondition of the openreport method is utilized, or have a look at FancyPrairies faq faq181-5497.

Roy-Vidar