how to use multi select list box to filter a query

[siamandm]

Hello
if i have a form call frmSearch , which contains a list box called lstbox and a button called cmdsearch, i want to filter a query called qryRpt using a column name location

how to do it please

many thanks

[HansV]

Do you want to use the query as record source for a report? If so, I would filter the report instead of the query.

[siamandm]

yes, i want to use it as a record source for a report

[HansV]

Create the following On Click event procedure for the command button:

Code: Select all

Private Sub cmdsearch_Click()
    Dim strIn As String
    Dim strWhere As String
    Dim itm As Variant
    For Each itm In Me.lstbox.ItemsSelected
        strIn = strIn & ",'" & Me.lstbox.ItemData(itm) & "'"
    Next itm
    If strIn <> "" Then
        strWhere = "location In (" & Mid(strIn, 2) & ")"
    End If
    DoCmd.OpenReport ReportName:="MyReport", View:=acViewPreview, WhereCondition:=strWhere
End Sub

where MyReport is the name of the report you want to open. I have assumed that location is a text field, and that the bound column of the list box corresponds to this field.
If it is a number field, change

Code: Select all

        strIn = strIn & ",'" & Me.lstbox.ItemData(itm) & "'"

to

Code: Select all

        strIn = strIn & "," & Me.lstbox.ItemData(itm)

[siamandm]

that was awesome
thanks a lot

[siamandm]

Create the following On Click event procedure for the command button:

Code: Select all

Private Sub cmdsearch_Click()
    Dim strIn As String
    Dim strWhere As String
    Dim itm As Variant
    For Each itm In Me.lstbox.ItemsSelected
        strIn = strIn & ",'" & Me.lstbox.ItemData(itm) & "'"
    Next itm
    If strIn <> "" Then
        strWhere = "location In (" & Mid(strIn, 2) & ")"
    End If
    DoCmd.OpenReport ReportName:="MyReport", View:=acViewPreview, WhereCondition:=strWhere
End Sub

where MyReport is the name of the report you want to open. I have assumed that location is a text field, and that the bound column of the list box corresponds to this field.
If it is a number field, change

Code: Select all

        strIn = strIn & ",'" & Me.lstbox.ItemData(itm) & "'"

to

Code: Select all

        strIn = strIn & "," & Me.lstbox.ItemData(itm)

thanks a lot for the code which is working perfectly ...
but how to combine this where condtion with a combo box selection(cboLocation)?? cbolocation will filter locationName field in the same query


the result should be shown based on the combo box and the list box

[HansV]

Does this do what you want?

Code: Select all

Private Sub cmdsearch_Click()
    Dim strIn As String
    Dim strWhere As String
    Dim itm As Variant
    ' Condition for lstbox
    For Each itm In Me.lstbox.ItemsSelected
        strIn = strIn & ",'" & Me.lstbox.ItemData(itm) & "'"
    Next itm
    If strIn <> "" Then
        strWhere = " AND location In (" & Mid(strIn, 2) & ")"
    End If
    ' Combine with condition for cboLocation
    If Not IsNull(Me.cboLocation) Then
        strWhere = strWhere & " AND locationName='" & Me.cboLocation & "'"
    End If
    If strWhere <> "" Then
        strWhere = Mid(strWhere, 6)
    End If
    DoCmd.OpenReport ReportName:="MyReport", View:=acViewPreview, WhereCondition:=strWhere
End Sub

[siamandm]

thank you for the quick reply.
im using this code below and get this error :

Code: Select all

Private Sub btnPrintPrivew_Click()

Dim itm As Variant
Dim strWhere As String
Dim strIn As String


For Each itm In Me.lstSection.ItemsSelected
strIn = strIn & ",'" & Me.lstSection.ItemData(itm) & "'"
Next itm
If strIn <> "" Then
strWhere = "Section_District_Name in (" & Mid(strIn, 2) & ")"
End If


' Combine with condition for cboLocation
    If Not IsNull(Me.cboLocation) Then
        strWhere = strWhere & " AND LocationName='" & Me.cboLocation & "'"
    End If
    If strWhere <> "" Then
        strWhere = Mid(strWhere, 6)
    End If



 DoCmd.OpenReport REPORTNAME:="rptToken", View:=acViewReport, WhereCondition:=strWhere
 

End Sub

Capture.PNG

[HansV]

Change the line

Code: Select all

strWhere = "Section_District_Name in (" & Mid(strIn, 2) & ")"

to

Code: Select all

strWhere = " AND Section_District_Name in (" & Mid(strIn, 2) & ")"

[siamandm]

the query return no result :(

i believe the issue is from the combo box ( its row source is a table ), and the combo box use the id instead of the string for filtering.

[HansV]

What is the name of the ID field? LocationNameID? If so, change the line

Code: Select all

        strWhere = strWhere & " AND LocationName='" & Me.cboLocation & "'"

to

Code: Select all

        strWhere = strWhere & " AND LocationNameID=" & Me.cboLocation

[siamandm]

sorry to make you confused, we have LocationName field in the query , and iside the form we have a cboLocation which its row source from a table LocationNameID,LocationName field...\so we have to make it the drop-down use the string location name in order to make the filter work

[HansV]

Try this:

Code: Select all

        strWhere = strWhere & " AND LocationName='" & Me.cboLocation.Column(1) & "'"

[siamandm]

thanks a lot now working as expected.

[siamandm]

Try this:

Code: Select all

        strWhere = strWhere & " AND LocationName='" & Me.cboLocation.Column(1) & "'"

i have another question please if i have another list box to combine with the first list box and the combo box, i tried this code below but i got the error: data type mismatch in criteria expression

the second list box called lstitm , and I want to use to filter a filed called ItemName_ID,
lstitme row source from a query its first field called ItemNameID

Code: Select all

Private Sub btnPrintPrivew_Click()
On Error GoTo ErrHandler
Dim itm As Variant
Dim strWhere As String
Dim strIn As String


For Each itm In Me.lstSection.ItemsSelected
strIn = strIn & ",'" & Me.lstSection.ItemData(itm) & "'"
Next itm
If strIn <> "" Then
strWhere = " AND Section_District_Name in (" & Mid(strIn, 2) & ")"
End If


For Each itm In Me.lstItm.ItemsSelected
strIn = strIn & ",'" & Me.lstItm.ItemData(itm) & "'"
Next itm
If strIn <> "" Then
strWhere = " AND ItemName_ID in (" & Mid(strIn, 2) & ")"
End If

' Combine with condition for cboLocation
    If Not IsNull(Me.cboLocation) Then
        strWhere = strWhere & " AND LocationName='" & Me.cboLocation.Column(1) & "'"
    End If
    If strWhere <> "" Then
        strWhere = Mid(strWhere, 6)
    End If




On Error GoTo ErrHandler
 DoCmd.OpenReport REPORTNAME:="rptToken", View:=acViewReport, WhereCondition:=strWhere
 Exit Sub
ErrHandler:
    If Err = 2501 Then
        ' Report canceled; ignore this error
    Else
        ' Display the error message
        MsgBox Err.Description, vbExclamation
    End If

End Sub

[HansV]

I assume that ItemName_ID is a number field, so you should omit the single quotes.

strIn = strIn & "," & Me.lstItm.ItemData(itm)

[siamandm]

this solved the issue partially!, which i mean it works only when i use the combo box and the new list box ... but when the combo box is selected, one item of the 1st list box and one item from the second list box is selected ... i get the error msg : Data Type mismatch in criteria expression

[HansV]

Code: Select all

Private Sub btnPrintPrivew_Click()
    On Error GoTo ErrHandler
    Dim itm As Variant
    Dim strWhere As String
    Dim strIn As String

    For Each itm In Me.lstSection.ItemsSelected
        strIn = strIn & ",'" & Me.lstSection.ItemData(itm) & "'"
    Next itm
    If strIn <> "" Then
        strWhere = " AND Section_District_Name in (" & Mid(strIn, 2) & ")"
    End If

    For Each itm In Me.lstItm.ItemsSelected
        strIn = strIn & "," & Me.lstItm.ItemData(itm)
    Next itm
    If strIn <> "" Then
        strWhere = strWhere & " AND ItemName_ID in (" & Mid(strIn, 2) & ")"
    End If

    If Not IsNull(Me.cboLocation) Then
        strWhere = strWhere & " AND LocationName='" & Me.cboLocation.Column(1) & "'"
    End If

    If strWhere <> "" Then
        strWhere = Mid(strWhere, 6)
    End If

    On Error GoTo ErrHandler
    DoCmd.OpenReport REPORTNAME:="rptToken", View:=acViewReport, WhereCondition:=strWhere
    Exit Sub

ErrHandler:
    If Err = 2501 Then
        ' Report canceled; ignore this error
    Else
        ' Display the error message
        MsgBox Err.Description, vbExclamation
    End If
End Sub

[HansV]

Please note that I edited my previous reply to correct a mistake. Please use the current version of the code.

[siamandm]

i get same issue :(
the previous code which was working partially, i notice that it only take one item into consideration and if you select multiple items .. it count only one item

when the combo box and the second listbox has a selected item it works ( only one item take into consideration)
but when the comobo box and the first list box has selected item i get the error mismatch