Passing several listbox multiselects to query

[hgus393]

Hi all!
I wonder if there is someone who knows how I can pass several listbox multiselects from a form to a query. I have some code that already passes one listbox with multiselects...can this code be adjusted to fit several listbox multiselects?

'Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
 
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("QueryFindWithDates>Q")
' Loop through the selected items in the list box and build a text string
    If Me!InstrList.ItemsSelected.Count > 0 Then
        For Each varItem In Me!InstrList.ItemsSelected
            strCriteria = strCriteria & Chr(34) _
                          & Me!InstrList.ItemData(varItem) & Chr(34)  Or "
 
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
     End If
 
 
 
 'Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM QueryFindWithDatesQ " & _
            "WHERE " & strCriteria & ";"
 
 
' Apply the new SQL statement to the query
    qdf.sql = strSQL
  ' Open the query
    DoCmd.OpenQuery "QueryFindWithDates>Q"
 
 
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

Thankful for any help

 

[vbaInet]

How many listboxes are we talking about?

What have you tried?

 

[hgus393]

Hi,

We're talking about 6 listboxes. I have tried just to pass one listbox (it works) and after that I get stuck I am afraid..
/Rob

 

[vbaInet]

This is how the WHERE part of the SQL statement should look like is this:

WHERE Field1 IN ('A', 'B', ...) [COLOR=Blue]AND[/COLOR] Field2 IN (1, 2, ...) [COLOR=Blue]AND[/COLOR] ... etc

The steps:
1. For the first listbox, check ItemsSelected.Count to see whether there are selections. If it is greater than 0 add "IN (" and loop through ItemsSelected and build the strings that will go in the brackets. Close the parens in the end, i.e. ")"
2. For the other listboxes, check ItemsSelected.Count again and if it's greater than 0 then add "AND FieldName IN (" then loop through ItemsSelected to build the strings that will go in the parens. Close the parens in the end, i.e. ")"

Try it with two listboxes and once you've gotten that working, apply the others.

 

[hgus393]

Ok that sounds like a plan! Cheers

 

[vbaInet]

Let us know how you get on.

I forgot to add, remember to add a space here --> "{Space here}AND FieldName IN ("