Listbox is not filtering records

[Lavenderchan]

Hello,

I am having problems with my code below. It will filter the form but its returning only a blank row. I do not think it is able to filter based on my query I am not sure why it is not able to select records from my query. What am I doing wrong?

Private Sub Command1611_Click()
Dim varItem As Variant
Dim strSearch As String
Dim task As String


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
    strSearch = strSearch & "[Building_Type_ID] = " & Chr(34) & Me!ListBuildingType.ItemData(varItem) & Chr(34) & "Or"
Next varItem
    If Len(strSearch) = 0 Then
        task = "select * from [q_Comparsion Data Form]  "
    Else
        strSearch = Left(strSearch, Len(strSearch) - 2) '' remove 2 charactors at the end, that is Or.
        task = "select * from [q_Comparsion Data Form] where (" & strSearch & ")"
    End If

   DoCmd.ApplyFilter task


    
End Sub

Thanks,
Keri

 

[dhookom]

The ApplyFilter might expect only the WHERE condition not the entire SQL statement.

I would simply set the filter property:

    If Len(strSearch) = 0 Then
        Me.FilterOn = False
    Else
        strSearch = Left(strSearch, Len(strSearch) - 2) [COLOR=#4E9A06]'' remove 2 charactors at the end, that is Or.[/color]
        debug.Print strSearch   [COLOR=#4E9A06]'add this to see what your code is doing[/color]
        Me.Filter = strSearch
        Me.FilterOn = True
    End If

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

 

[Lavenderchan]

Hi dhookom,

I get the following from the debug.print

[Building_Type_ID] = "1"Or[Building_Type_ID] = "2"

What does this mean? It is not filtering with the changes either. It is returning a blank row.

 

[dhookom]

Is building_Type_ID numeric or string? Your code should add spaces both sides of OR.

 strSearch = strSearch & "[Building_Type_ID] = " & Chr(34) & Me!ListBuildingType.ItemData(varItem) & Chr(34) & " Or "

and then

strSearch = Left(strSearch, Len(strSearch) - 4) [COLOR=#4E9A06]' remove 4 charactors at the end, that is Or.[/color]

What do you get if you create a new blank query and paste this into the SQL view:

select * from [q_Comparsion Data Form] where [Building_Type_ID] = "1" Or [Building_Type_ID] = "2"

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

 

[Lavenderchan]

To anwnser you questions

I assume Building_Type_ID is a string. In the query Building_Type_ID is a text field. The list box pulls its data from another table and the Building_Type_ID is the primary key.

When I created the query it return no records only the column headers and blank records.

 

[dhookom]

Apparently no records meet the criteria. Remove the criteria and check the values.

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