Basic Coding Question - Novice

[PinkLady50]

I am trying to create a report that allows the user to select multiple contractors. I found this code and it works except for the last line. I know this is pretty simple but I don't know how to fix it. The code in RED is what is not working.

Private Sub cmdPerfIssuesContractorRpt_Click()
  On Error GoTo Err_cmdPerfIssuesContractorRpt_Click
  Dim strWhere      As String
  Dim ctl           As Control
  Dim varItem       As Variant
  'make sure a selection has been made
  If Me.cboContractorSelection.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
  End If
  'add selected values to string
  Set ctl = Me.cboContractorSelection
  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 "rptPerfIssuesbyContractor", acPreview, , "Contractor IN(" & strWhere & ")"
Exit_cmdPerfIssuesContractorRpt_Click:
  Exit Sub
Err_cmdPerfIssuesContractorRpt_Click:
  MsgBox Err.Description
  Resume Exit_cmdPerfIssuesContractorRpt_Click
End Sub

[andy49]

How about using the like command

DoCmd.OpenReport "rptPerfIssuesbyContractor", acPreview, , "Contractor like ('*" & strWhere & "'*)"

[PinkLady50]

It still says "Missing operator in query expression"

[pbaldy]

I don't think Like is appropriate in this case. What's the data type of the Contractor field? If it's text, you need delimiters. Note the second commented out strWhere:

http://www.baldyweb.com/multiselect.htm

[isladogs]

If this is a multiselect listbox or combobox, you need the IN operator
There needs to be a space after IN as shown below

If it still fails, suggest you add the line in BLUE to check the output

'trim trailing comma  
strWhere = Left(strWhere, Len(strWhere) - 1)
Debug.Print strWhere

 'open the report, restricted to the selected items   
DoCmd.OpenReport "rptPerfIssuesbyContractor", acPreview, , "Contractor IN (" & strWhere & ")"

[PinkLady50]

Paul
I am not getting any errors but I am also not getting any data in the report. This report was originally setup for a single Contractor selection and I know the report worked correctly. Now that I am adding a multiple selection to the report no data is coming up, only the headings.

[pbaldy]

Are you sure the bound column of the listbox returns the values expected in the where clause? Can you attach the db here?

[davegri]

If your cboContractorSelection is actually a combobox, I don't think this will apply, as I believe a multiselect combobox has to do with multi-valued fields, which I do not use.
The below code is for a listbox. Added code to replaces the right side comma with a close parens.
You may have to adjust the red index to get the correct column in the row.

Public Sub genWhere()
Dim sq As String
Dim varItem As Variant
Dim strWhere As String
sq = Chr$(39)      'single quote


strWhere = "Contractor In("
For Each varItem In cboContractorSelection.ItemsSelected
    strWhere = strWhere & sq & Me.cboContractorSelection.Column(1, varItem) & sq & ","
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 1) & ")"
Debug.Print strWhere
DoCmd.OpenReport "rptPerfIssuesbyContractor", acPreview, , strWhere
End Sub

[isladogs]

@davegri - good point
Mutliselect combo boxes are indeed based on the dreaded multivalue fields so if you need part of them you will need to treat as arrays and use the split function

As for listboxes, the code I provided in post 3 should be correct & it trims off the trailing comma
Your code appears to be just a variation on that