Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Listbox is not filtering records

Status
Not open for further replies.

Lavenderchan

Technical User
Jul 21, 2008
132
US
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?

Code:
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
 
The ApplyFilter might expect only the WHERE condition not the entire SQL statement.

I would simply set the filter property:

Code:
    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
 
Hi dhookom,

I get the following from the debug.print

Code:
[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.
 
Is building_Type_ID numeric or string? Your code should add spaces both sides of OR.

Code:
 strSearch = strSearch & "[Building_Type_ID] = " & Chr(34) & Me!ListBuildingType.ItemData(varItem) & Chr(34) & " Or "
and then
Code:
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:

SQL:
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
 
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.
 
Apparently no records meet the criteria. Remove the criteria and check the values.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top