Syntax Error (Mission operator) in Query Expression 2

[Lavenderchan]

Hello I am getting a syntax error in my code while tying to filer with a list box.
Here is my code:
Sub Search2()
Dim strSearch, strCriteria, strBuilding_Type, strCounty As String
Dim Task As String
Dim varItem As Variant
'''=========== code for Building Type List box
For Each varItem In Me!ListBuildingType.ItemsSelected
''' use ASCII Chr(34) to replace Double Quotes in VB
'''Chr(34) = "
''' Use "Or" to get result from all states
strBuilding_Type = strBuilding_Type & "[Building_Type] = " & Chr(34) & Me!ListBuildingType.ItemData(varItem) & Chr(34) & "Or"
Next varItem
MsgBox (strBuilding_Type)
If Len(strBuilding_Type) > 0 Then
strBuilding_Type = Left(strBuilding_Type, Len(strBuilding_Type) - 2)
strCriteria = strCriteria & " And (" & strBuilding_Type & ")"
End If
MsgBox (strCriteria)

'''=========== code for Customer type combined from list box and combo box
If IsNull(Me.cboCounty) Or Me.cboCounty = "" Then
' do nothing
ElseIf Me.cboCounty = 390 Then ' for Select Multiple
strSearch = "[CountyID] in (" & TempVars!tempcounty & ")"
strCriteria = strCriteria & " And (" & strSearch & ")"
Else
strSearch = "([CountyID] = " & Me.cboCounty & ")" ''' for item selected from combo box
strCriteria = strCriteria & " And (" & strSearch & ")"
End If

Task = "select * from [tblComparison Data] where (" & strCriteria & ")"
MsgBox Task
Me.FilterOn = True
[highlight #FCE94F]DoCmd.ApplyFilter Task[/highlight]

End Sub
There error is happening at the apply filter task
For each of the msgbox boxes I have added to figure out where the error it self is coming from I am not sure what is causing it. the error states Syntax Error (Missing operator) in Query Expression '(And([Building_Type]="3"Or[Building_Type]="8"))'.
Any information to help fix this is greatly appreciated.

 

[Andrzejek]

Instead of:[tt]
MsgBox Task[/tt]
do:[tt][blue]
Debug.Print [/blue]Task[/tt]
and show here what you see in the Immediate Window. (Copy and Paste it here)


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Please post your code like this for easier reading:

Sub Search2()
	Dim strSearch, strCriteria, strBuilding_Type, strCounty As String
	Dim Task As String
	Dim varItem As Variant
	[COLOR=#4E9A06]'''=========== code for Building Type List box[/color]
	For Each varItem In Me!ListBuildingType.ItemsSelected
		[COLOR=#4E9A06]'   use ASCII Chr(34) to replace Double Quotes in VB
		'   Chr(34) = "
		'   Use "Or" to get result from all states[/color]
		strBuilding_Type = strBuilding_Type & "[Building_Type] = " & Chr(34) & Me!ListBuildingType.ItemData(varItem) & Chr(34) & "Or"
	Next varItem
	MsgBox (strBuilding_Type)
	If Len(strBuilding_Type) > 0 Then
		strBuilding_Type = Left(strBuilding_Type, Len(strBuilding_Type) - 2)
		strCriteria = strCriteria & " And (" & strBuilding_Type & ")"
	End If
	MsgBox (strCriteria)

	[COLOR=#4E9A06]'  =========== code for Customer type combined from list box and combo box[/color]
	If IsNull(Me.cboCounty) Or Me.cboCounty = "" Then
		[COLOR=#4E9A06]' do nothing[/color]
	 ElseIf Me.cboCounty = 390 Then [COLOR=#4E9A06]' for Select Multiple[/color]
		strSearch = "[CountyID] in (" & TempVars!tempcounty & ")"
		strCriteria = strCriteria & " And (" & strSearch & ")"
	 Else
		strSearch = "([CountyID] = " & Me.cboCounty & ")" [COLOR=#4E9A06]''' for item selected from combo box[/color]
		strCriteria = strCriteria & " And (" & strSearch & ")"
	End If

	Task = "select * from [tblComparison Data] where (" & strCriteria & ")"
	MsgBox Task
	Me.FilterOn = True
	DoCmd.ApplyFilter Task

End Sub

The DoCmd.ApplyFilter accepts a where condition, not typically the entire SQL statement. It looks like you should be setting the Record Source property.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Lavenderchan]

Hello Andrzejek
Debug.print task returned the following:

select * from [q_Comparsion Data Form] where ( And ([Building_Type] = "3"Or[Building_Type] = "7"O))

 

[MajP]

Debug.print task returned the following:
select * from [q_Comparsion Data Form] where ( And ([Building_Type] = "3"Or[Building_Type] = "7"O))

That is not possible. Your code has

Task = "select * from [tblComparison Data] where

How does this code become

select * from [q_Comparsion Data Form] where

Anyways that string is obviously bad. You need to write your code to get something like

select * from [tblComparison Data] where ( ([Building_Type] = "3" Or [Building_Type] = "7" ))

I assume building type is a numeric field so

select * from [tblComparison Data] where [Building_Type] = 3 Or [Building_Type] = 7

 

[Andrzejek]

Well, your SQL:
[tt]
select * from [q_Comparsion Data Form]
where ( And ([Building_Type] = "3"Or[Building_Type] = "7"O))
[/tt]
has some missing parts no matter where you want to use it.
Step thru your code and see how you build it.

You may want to use [blue]this trick[/blue]:[tt]
Task = "select * from [tblComparison Data] where [blue]1 = 1[/blue] " & strCriteria
[/tt] so all other criteis for your Select can start with " AND ..."

And, like Duane is saying, you may need just the WHERE part of it in your [tt]DoCmd.ApplyFilter [/tt]

Good catch MayP with [tt]q_Comparsion Data Form[/tt] vs. [tt]tblComparison Data[/tt]

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

In addition, I would use a generic function for use with multi-select list boxes:

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
[highlight #D3D7CF][COLOR=#4E9A06]    'send in
    '      List box control object
    '      Name of the field
    '      Delimiter which is typically a single quote for text[/color][/highlight]
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        [highlight #D3D7CF][COLOR=#4E9A06]'remove the last ", " and add the ")"[/color][/highlight]
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn

End Function

Other code can clear any list box:

Function ClearList(lst As ListBox) As Boolean
On Error GoTo Err_ClearList
[highlight #D3D7CF]   [COLOR=#4E9A06] 'Purpose:   Unselect all items in the listbox.
    'Return:    True if successful
    'Author:    Allen Browne. [URL unfurl="true"]http://allenbrowne.com[/URL]  June, 2006.[/color][/highlight]
    Dim varItem As Variant

    If lst.MultiSelect = 0 Then
        lst = Null
    Else
        For Each varItem In lst.ItemsSelected
            lst.Selected(varItem) = False
        Next
    End If

    ClearList = True

Exit_ClearList:
    Exit Function

Err_ClearList:
    [highlight #D3D7CF][COLOR=#4E9A06]'Call LogError(Err.Number, Err.Description, "ClearList()")[/color][/highlight]
    Resume Exit_ClearList
End Function

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Untested, but I think it is closer. Assuming building types are really numbers.

        Dim strSearch as strin
        Dim strCriteria as string 
        Dim strBuilding_Type as string
        Dim strCounty As String
	Dim varItem As Variant
        'add reference to strBuilding type

        '''=========== code for Building Type List box
	'assuming building type is Numeric get rid of double quotes
        For Each varItem In Me!ListBuildingType.ItemsSelected
		'   use ASCII Chr(34) to replace Double Quotes in VB
		'   Chr(34) = "
		'   Use "Or" to get result from all states
		strBuilding_Type = strBuilding_Type & "[Building_Type] = " & Me!ListBuildingType.ItemData(varItem) & "Or"
	Next varItem
	
	If Len(strBuilding_Type) > 0 Then
		strBuilding_Type = Left(strBuilding_Type, Len(strBuilding_Type) - 2)
                'Take the extra Or out, but put an AND in case of country criteria
                strBuilding_Type = "(" & strBuilding_Type & ") AND "
        End If
	

	'  =========== code for Customer type combined from list box and combo box
	If not Trim(Me.cboCounty & " ") = "" then
	  'Add a bogus And because you need to strip it off anyways if no country part	
	  If Me.cboCounty = 390 Then ' for Select Multiple
		strCountry = " [CountyID] in (" & TempVars!tempcounty & ") AND"
	  Else
		strCountry = " ([CountyID] = " & Me.cboCounty & ") AND" 
		
	  end if
        End If
        strCriteria = strBuildType & strCountry
        if len(strCriteria) > 0 then
          strCriteria = Left(strCriteria, Len(strCriteria) - 3)
	  debug.print strCriteria
          'a filter is a where string without the keyword Where
          Me.filter = strCriteria
	  debug.print me.filter
	  Me.FilterOn = True
	end if

I make complex filter forms like this with many, many controls. I have one with about 20 controls. My trick is always the same. For each control used as a search criteria I define a variable to return its part of the criteria. I also like to give the choice to AND them together or OR them together

Dim strType As String
  Dim strLocation As String
  Dim strSeries As String
  Dim strStatus As String
  ....... More strings per control
  Dim andOR As String
  Dim removeEnd As Integer
  
     If Me.framAndOr.Value = 1 Then
      andOR = " OR "
      removeEnd = 4
    Else
      andOR = " AND "
      removeEnd = 5
    End If
    
   
    If Not Trim(Me.cboLocation & " ") = "" Then
        strLocation = "[Location] = '" & Me.cboLocation & "'" & andOR
        'Debug.Print "location " & strLocation & vbCrLf
    End If
    
    If Not Trim(Me.cboSeries & " ") = "" Then
        strSeries = "[TIR_Series_ID_FK] = " & Me.cboSeries & andOR
       ' Debug.Print "series " & strSeries
    End If
    
    If Not Trim(Me.cboStatus & " ") = "" Then
        strStatus = "[status] = '" & Me.cboStatus & "'" & andOR
    End If
   
   ... I can have different control types to include multi selects, checkboxes, textboxes as long as it returns a part of criteria string ending with an AND or OR
   'Then put it together and since every string ended in either AND or OR I remove that
   getFilter = (strLocation & strStatus & strSeries ........)
    If Not Trim(getFilter & " ") = "" Then
      getFilter = Left(getFilter, Len(getFilter) - removeEnd)
    End If

So If I want to add another control to the form I add a variable for the criteria, build the criteria, and add it to the concatenation.