SQL statement in Report

[Rhondann]

I'm trying to get information from a list box to run a report to select criteria. But I can't get the SQL statement right. I copied this code off the internet and I'm trying to configure it to work for my report.
I'm getting a "Error 3705 =- Syntax Error Missing operator in query expression


http://allenbrowne.com/ser-50.html


Private Sub cmdQuery_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type. See note 1.
strDoc = "Comprehensive Regional Results Events"

'Loop through the ItemsSelected in the list box.
With Me.lstRegion
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
'lngLen = Len(strWhere) - 1
If lngLen > 0 Then

'This is the problem line
strWhere = "[Region] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2


End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

 

[grnzbra]

I think your problem is in the setting of strDelim. You need strWhere to look like "[Region] IN ('Value1','Value2','Value3')"
The way strDelim is being set, it looks like you would get something like

"[Region] IN ("Value1","Value2","Value3")" or perhaps "[Region] IN (""Value1"",""Value2"",""Value3"")"

Put in a break point at the problem line and look at what is in strWhere.

I think you need

strDelim = "'"

 

[Rhondann]

You were right, but how would I

Thank you for your reponse. You were exactly right, that was the answer to the problem. How would I code it if I didnt' want to have the 2 fields, and I wanted to take the description out of this code. I was trying to do it, and I was getting an error.

 

[grnzbra]

I'm not sure what you are asking?
Which line of code is referring to the "2 fields"?
Which line of code has the description you are trying to remove. Are you talking about strDescrip? (is this the second field). You aren't setting it anywhere so I would expect when you try to set the length to Len(strDescrip)-1 it would blow up. Also, it is then used as a parameter (OpenArgs)when you open the report. Check is the report really needs this parameter. If not, delete, the Len(strDescrip) line and the OpenArgs section of the call to the report. Otherwise, you need to set it equal to something.