Syntax Error In Code . .

[Evagrius]

Hello - I found this code that seemed to meet my need in allowing me to filter a report through a multi-select list box prior to opening the report. For some reason, I keep getting a "Syntax Error missing operator in query expression." Can anyone please help?? The error is on the Docmd line . . .

 Dim strWhere      As String
  Dim ctl           As Control
  Dim varItem       As Variant
  'make sure a selection has been made
  If Me.ListYear.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
  End If
  'add selected values to string
  Set ctl = Me.ListYear
  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 "UNIQUE REPORT", acPreview, , "Year Invested IN(" & strWhere & ")"

 

[boblarson]

In this line you need square brackets around the field name which has spaces:

DoCmd.OpenReport "UNIQUE REPORT", acPreview, , "[Year Invested] IN(" & strWhere & ")"

 

[Evagrius]

Thank you Bob!!! Your response solved the problem! The brackets were missing!

 

[boblarson]

Is the year a number or text? If text then you need to include quotes around the item:

  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & [COLOR="Red"][B]Chr(34) &[/B][/COLOR] ctl.ItemData(varItem) [COLOR="red"][B]& Chr(34)[/B][/COLOR] &  ","
  Next varItem

 

[Evagrius]

PERFECT! Thank you for the lesson - now I know!!