Multi-select listbox to generate report - runtime error 3075

[Scaniafan]

Good morning all,

I am trying to use an multi-select listbox to generate a report for the selected forwarding agents only. I'm copying the code from http://www.baldyweb.com/multiselect.htm, changed this part of the code as the values are text in the table where the report is created from:

  strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

My code now looks as below:

    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant
    
    'make sure a from date has been entered
    If IsNull(From_Date) Then
      MsgBox "From date cannot be blank."
      Exit Sub
    End If
    
    'make sure a till date has been entered
    If IsNull(Till_Date) Then
      MsgBox "Till date cannot be blank."
      Exit Sub
    End If
    
    'make sure a selection has been made
    If Me.Forwarder_List.ItemsSelected.Count = 0 Then
      MsgBox "At least one forwarding agent must be selected."
      Exit Sub
    End If
    
    'add selected values to string
    Set ctl = Me.Forwarder_List
    For Each varItem In ctl.ItemsSelected
      strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
      'Use this line if your value is not text
      '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 "RPT_Net_Performance", acPreview, , "Forwarding agent IN(" & strWhere & ")"

However, I keep getting the message:

Runtime error '3075':

Syntax error (missing operator) in query expression 'Forwarding Agent IN('DBSAIRCH','DBSAIRCHIM')'.

I've tried using a semicolon as seperator, but it gives me the same error message. Can someone help me on this?

 

[arnelgp]

DoCmd.OpenReport "RPT_Net_Performance", acPreview, , "[Forwarding agent] IN (" & strWhere & ")"

 

[Scaniafan]

Thanks, that solved it!