CODEWITHSUNDEEP
vbams-access
CryingOverVBA
CryingOverVBA

Reputation: 35

Issues filtering a listbox using variables

I'm unsure whether or not this is the best way to accomplish what I'm attempting to accomplish, but I'm currently attempting to alter the active filters on a listbox using multiple text boxes.

I have one main search box that filters an initial list box, this will also filter a few of my other list boxes that also populate relevant data.

When I look into my other list boxes (not my main one) I have data that I would like to filter further. So, I go into another textbox and input whatever it is I'm attempting to sort out, when I click the second "search button" this is where I get my error. It's a syntax error, but I don't fully understand the syntax behind it.

I'd like for my initial filter to be kept, in addition to my new criteria for searching.

Here's the code behind my initial search button (which this works)

Private Sub Command37_click()
    Dim sql As String
    Dim sql2 As String

sql = "SELECT People.LName, People.[Phone #], People.State " _
    & "FROM People" _
    & "WHERE SystemLocation LIKE '*" & Me.SearchTxt1 & "*' " _
    & "ORDER by People.LName "

    me.List35.RowSource = sql
    me.list35.requery

sql2 = "SELECT Orders.Item, Orders.Price, Orders.Department " _
     & "FROM People INNER JOIN Orders " _
     & "ON People.SystemLocation = Orders.Department " _
     & "WHERE Department LIKE '*" & Me.SearchTxt1 & "*' " _
     & "ORDER by Orders.Department"

    Me.List41.RowSource = sql2
    Me.List41.Requery

Here's the code that I was trying to use with the secondary filter

Dim sql2 As String

sql2 = "SELECT Orders.Item, Orders.Price, Orders.Department " _
         & "FROM People INNER JOIN Orders " _
         & "ON People.SystemLocation = Orders.Department " _
         & WHERE (People.SystemLocation LIKE '*" & Me.SearchTxt1 & "*') & (Orders.Item LIKE '*" & Me.SearchTxt2 & "*' " _
         & "ORDER by Orders.Department"

Me.List41.RowSource = sql2
Me.List41.Requery

Ideally I'd like to be able to create a "search engine" of sorts.

Upvotes: 0

Views: 56

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

The syntax error is surrounding your where clause.

Firstly, you are missing a double-quote here:

& WHERE (People.SystemLocation LIKE '*" & Me.SearchTxt1 & "*') & (Orders.Item LIKE '*" & Me.SearchTxt2 & "*' " _
 ^----- Double quote missing

But the main problem is that you are using the ampersand concatenation operator (&) to represent a logical and statement:

"*') & (Orders.Item LIKE '*"
     ^----- This should be AND

Instead, you should use the and operator, e.g.:

& "WHERE (People.SystemLocation LIKE '*" & Me.SearchTxt1 & "*') AND (Orders.Item LIKE '*" & Me.SearchTxt2 & "*' " _

However, in this case, the concatenation of form control values isn't required (and opens the potential for SQL injection) since you can directly reference a form value from the Row Source of the list box.

As such, the code can become:

Me.List41.RowSource = _
    "SELECT Orders.Item, Orders.Price, Orders.Department " & _
    "FROM People INNER JOIN Orders ON People.SystemLocation = Orders.Department " & _
    "WHERE " & _
    "    People.SystemLocation LIKE '*' & Forms![YourForm]!SearchTxt1 & '*' AND " & _
    "    Orders.Item LIKE '*' & Forms![YourForm]!SearchTxt2 & '*' " & _
    "ORDER by Orders.Department"
Me.List41.Requery

Here, change [YourForm] to the name of your form.

Upvotes: 1

Related Questions