Sql Where Clause By List Box

[jasn_78]

I have a where clause in a report that works fine across numereous combo boxes

Dim strWHERE As String

'set where criteria to nothing
strWHERE = ""

'set where conditions for sql
'if dept combobox not empty filter by dept
    If Forms!frmITEM.comboDEPT <> "" Then
    strWHERE = strWHERE & "ITEMTBL.ITEM_IDEP = forms!frmITEM.comboDEPT"
    End If
'if subdept not empty filter by subdept
    If Forms!frmITEM.comboSUBD <> "" Then
        If strWHERE <> "" Then
        strWHERE = strWHERE & " AND ITEMTBL.ITEM_ISDP = forms!frmITEM.comboSUBD"
        Else
        strWHERE = strWHERE & " ITEMTBL.ITEM_ISDP = forms!frmITEM.comboSUBD"
        End If
    End If
'if group not empty filter by group
    If Forms!frmITEM.comboGROUP <> "" Then
        If strWHERE <> "" Then
        strWHERE = strWHERE & " AND ITEMTBL.ITEM_IGRP = forms!frmITEM.comboGROUP"
        Else
        strWHERE = strWHERE & " ITEMTBL.ITEM_IGRP = forms!frmITEM.comboGROUP"
        End If
    End If
'if fromplu not empty choose all records greater than or equal to this number
    If Forms!frmITEM.txtPLUFROM <> "" Then
        If strWHERE <> "" Then
        strWHERE = strWHERE & " AND ITEMTBL.ITEM_NUMBER >= forms!frmITEM.txtPLUFROM"
        Else
        strWHERE = strWHERE & " ITEMTBL.ITEM_NUMBER >= forms!frmITEM.txtPLUFROM"
        End If
    End If
'if toplu not empty choose all records less than or equal to this number
    If Forms!frmITEM.txtPLUTO <> "" Then
        If strWHERE <> "" Then
        strWHERE = strWHERE & " AND ITEMTBL.ITEM_NUMBER <= forms!frmITEM.txtPLUTO"
        Else
        strWHERE = strWHERE & " ITEMTBL.ITEM_NUMBER <= forms!frmITEM.txtPLUTO"
        End If
    End If

'report record source
'if strwhere empty select all items
    If strWHERE = "" Then
    Me.RecordSource = "SELECT ITEM_NUMBER,ITEM_DESC " & _
                      "FROM ITEMTBL"
    Else
'if strwhere any value use it as a where sql clause
    Me.RecordSource = "SELECT ITEM_NUMBER,ITEM_DESC " & _
                            "FROM ITEMTBL " & _
                            "WHERE " & strWHERE & _
                            " ORDER BY ITEM_NUMBER"
    End If

but what i would like to do is use a listbox with multiple selections instead of each combobox e.g instead of comboDEPT i would like a listDEPT but be able to filter by the multiple selections i make off that list box.

Not sure how i would do this. The other thing i am trying to grasp on that same process is these listboxes would be on a seperated form (not a subform) as it would just take too much space on that form to have all the list boxes i require.

any ideas would be appreciated.

 

[pbaldy]

This is not exactly on point, but perhaps points you in the right direction?

http://www.baldyweb.com/multiselect.htm

 

[jasn_78]

paul thanks, so if i store the results from my listboxes in a query would i say for filtering by department ITEMTBL.ITEM_IDEP in qryDEPT?

and then would this work if i wanted to say do department and group by using

strcriteria = "ITEMTBL.ITEM_IDEP in qryDEPT AND ITEMTBL.ITEM_IGRP in qryGRP"

thanks mate