Syntax error (missing operator) in query expression

Question

I am having issues with the vba code for a command button on a form that I have. I am getting an error that reads: Syntax error (missing operator) in query expression '{Type of Services} IN (x,z)Region =1'.

Below is the code I am trying to use to make the button create a report to open.

Private Sub cmdCreateReport2_Click()

Dim varItem As Variant

Dim strFilter As String

If Me.lstCategory.ItemsSelected.Count > 0 Then

For Each varItem In Me.lstCategory.ItemsSelected

strFilter = strFilter & "," & Me.lstCategory.ItemData(varItem)

Next varItem

'remove leading comma and space

strFilter = Mid(strFilter, 2)

strFilter = "[Type of Services] IN (" & strFilter & ")"

End If

If Len(strFilter) > 0 And Not IsNull(Me.cboregion) Then

strFilter = strFilter & "Region = " & Me.cboregion

End If

DoCmd.OpenReport "Multiple Categories by Region", _

View:=acViewNormal, _

WhereCondition:=strFilter

DoCmd.Close acForm, "Multiple Categories By Region"

End Sub

________________________________________________________________________________

It is highlighting the:

DoCmd.OpenReport "Multiple Categories by Region", _

View:=acViewNormal, _

WhereCondition:=strFilter

in the debug report.

Combo Box Name: CboRegion

List box Name: lstCategory

Command Button Name: cmdCreateReport2

query/report/form name: Multiple Categories by Region

Region List Table Name: tblRegionList = this is a numerical field > my combo box is built of this

Category List Table Name: tblCategoryList = this a text field > my listbox is built of this

query is built on a table named: Resources List

Inside the query, I have the criteria set for 

Region: [Forms]![Multiple Categories By Region]![CboRegion]

Types of Services Provided: [Forms]![Multiple Categories By Region]![LstCategory]

Could someone please help me figure out where I am going wrong. The other thing I can't figure out either is it is wanting to print the report instead of just opening it. Any help would be greatly appreciated.

Thanks,

Erica

Answer 1

Thank you for replying. I am very novice at writing code, and in fact what I have has is pieced together by using search engine results. And am not even certain I have all of the coding I need in there. 

My intention was there would always be a region entered and at least one type of service the user would be looking for.

I took your suggestion, made the modification and it is still giving me the same error.

Is it possible that it is not how the code is written at this point, but how I have the query set up to generate the report? I've never used a listbox on a form before...  Under a query criteria line, I used the same format that I would use for a combo box.

[Forms]![Multiple Categories By Region]![LstCategory]

Is the error really exactly the same?  What does it show for the query expression where the error occurs?

Do you get the exact same error when you open the report directly from the navigation pane (so long as you have the form open when you open it)?  If so, then there's an error in the recordsource query of the report.  If not, then the syntax error is in the filter string your code is building.  Please post that code as it stands now.  Also, insert the line:

    Debug.Print strFilter

... right before the DoCmd.OpenReport line, and see what is displayed in the Immediate Window when you click the button to open the report.  If it isn't obvious where the trouble is, post that output here for us to look at.

I notice you said your recordsource query has a criterion "[Forms]![Multiple Categories By Region]![LstCategory]".  Isn't that the same as what you are using to build your filter criteria in code?  If so, then you don't need that criterion in the query.  Further, if the list box is a multiselect list box as your code implies, then you can't use it as a criterion in the query like that, because its value will always be Null.  Only single-select list boxes have a value.

Answer 2

What you need to do is add this line of code before the OpenReport:

Debug.Print strFilter

This will print the contents of strFilter to the immediate Window. From there, you can see what it actually says and see if there is a missing operator somewhere.

Answer 3

Thank you for replying. I am very novice at writing code, and in fact what I have has is pieced together by using search engine results. And am not even certain I have all of the coding I need in there. 

My intention was there would always be a region entered and at least one type of service the user would be looking for.

I took your suggestion, made the modification and it is still giving me the same error.

Is it possible that it is not how the code is written at this point, but how I have the query set up to generate the report? I've never used a listbox on a form before...  Under a query criteria line, I used the same format that I would use for a combo box.

[Forms]![Multiple Categories By Region]![LstCategory]

Answer 4

The other thing I can't figure out either is it is wanting to print the report instead of just opening it.

I overlooked this the first time through.  Your code explicitly says to print the report:

    DoCmd.OpenReport "Multiple Categories by Region", _

View:=acViewNormal, _

WhereCondition:=strFilter

"View:=acViewNormal", for a report, is printing it.  If you want it opened in print preview, use

View:=acViewPreview, _

Answer 5

At least one problem is that you have no conjunction between the two criteria you are adding to strFilter.  If you are filtering by both [Type of Services] and Region, then you need " AND " between those two criteria.  So where you have this code:

    If Len(strFilter) > 0 And Not IsNull(Me.cboregion) Then

strFilter = strFilter & "Region = " & Me.cboregion

End If

... you should change it to:

     If Len(strFilter) > 0 And Not IsNull(Me.cboregion) Then

strFilter = strFilter & " AND Region = " & Me.cboregion

End If

Note: so far as I can see, your code doesn't let the user filter by Region unless they are also filtering by [Type of Services].  Is that your intention?