Hi Pbaldy,
I was having some syntax error issues (crossing over from Access 2003 to Access 2010) with my multi select listbox. I tried using your code from your Baldyweb site, it helped to eliminate the syntax error, however I cannot get your code to filter my report, instead it shows all records. Could you please have a look at my code & see where the error could be:
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.List58.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one contract to build report.", vbCritical, "Message from ZP"
Exit Sub
End If
'add selected values to string
Set ctl = Me.List58
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptMarginReport", acPreview, , "ContractID IN(" & strWhere & ")"
Exit_cmdPreview_Click:
Exit Sub
Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click
End Sub
The query SQL is:
SELECT tblContract.ContractID, tblLocal.LoadLineID, tblLocal.Driver, tblLocal.DeliveryDate, tblLocal.Qty, tblLocal.SubRate, tblLocal.SubPayment, tblLocal.OrderID, tblLocal.Suburb, tblContract.Contract, tblLocal.Yard, tblLocal.Plant, tblLocal.MinQty, tblDrivers.DriverID, tblLocal.AreaKlm, tblLocal.BrickType, tblLocal.WorkSortOrder, tblLocal.DocketNo, tblLocal.TimberSisFlexi, tblLocal.JDRate, tblLocal.SubRate, tblLocal.JDPayment, tblLocal.SubPayment, tblLocal.Balance, tblDrivers.SubContractor, tblDrivers.GroupNo, tblDrivers.GroupName, tblLocal.BrickType, IIf([GroupNo]=1,"JD Drivers",IIf([GroupNo]>1,"All Other Subys","All Other Subys")) AS GroupNameforReport, tblLocal.AmountPaid1, tblLocal.AmountPaid2, tblLocal.OutstandingBalance, tblContract.ReportableName
FROM (tblLocal LEFT JOIN tblContract ON tblLocal.ContractID = tblContract.ContractID) LEFT JOIN tblDrivers ON tblLocal.Driver = tblDrivers.DriverName
WHERE (((tblLocal.Driver) Is Not Null) AND ((tblLocal.BrickType) Not Like "PP" And (tblLocal.BrickType) Not Like "C/OUT" And (tblLocal.BrickType) Not Like "M*M" And (tblLocal.BrickType) Not Like "PPTF")) OR (((tblLocal.Driver) Is Not Null) AND ((tblLocal.BrickType) Is Null))
ORDER BY tblLocal.WorkSortOrder;
is this not working correctly due to the joins in the query? ContractID is numerical.
Many thanks!