Multi Select list box

[Lyncroft]

I've managed to get 2 list boxes on one form to work properly on a query, eg, they take any combinations I put in (at least I think they do) including if I leave one list box unset.

My problem is getting the code to work for a third list box. Here is my code and any advice gratefully received. I'm sure its something with the SQL statement but don't know what. I would like the 3 list boxes to tackle any combination entered, including leaving some boxes unset (if poss)!!

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Dim sConsultation1 As String
Dim sStakeholder1
Dim sCountry1 As String
Dim sCriteria As String
Dim varItem As Variant

' build criteria string for selected consultation
For Each varItem In Me.lstConsultation1.ItemsSelected
sConsultation1 = sConsultation1 & ",'" & Me.lstConsultation1.ItemData(varItem) & "'"
Next varItem
sConsultation1 = Mid(sConsultation1, 2) ' remove leading comma.
sConsultation1 = " [Consultation] in (" & sConsultation1 & ")"

' build criteria string for selected item stakeholder.
For Each varItem In Me.lstStakeholder1.ItemsSelected
sStakeholder1 = sStakeholder1 & ",'" & Me.lstStakeholder1.ItemData(varItem) & "'"
Next varItem
sStakeholder1 = Mid(sStakeholder1, 2) ' remove leading comma.
sStakeholder1 = " [Stakeholder] in (" & sStakeholder1 & ")"


' build criteria string for selected country
For Each varItem In Me.lstCountry1.ItemsSelected
sCountry1 = sCountry1 & ",'" & Me.lstCountry1.ItemData(varItem) & "'"
Next varItem
sCountry1 = Mid(sCountry1, 2) ' remove leading comma.
sCountry1 = " [Country] in (" & sCountry1 & ")"

' build SQL Statement.
If Me.lstConsultation1.ItemsSelected.Count > 0 And _
Me.lstCountry1.ItemsSelected.Count > 0 And _
Me.lstStakeholder1.ItemsSelected.Count > 0 _
Then
sCriteria = sConsultation1 & " AND " & sCountry1 & " AND " & sStakeholder1
Else
sCriteria = IIf(Me.lstConsultation1.ItemsSelected.Count > 0, sCountry1, sStakeholder1)
End If
SQL = "Select Distinct Organisation, Name, Consultation, Stakeholder, Country " & _
" FROM qryAll " & _
" WHERE " & sCriteria

Set db = CurrentDb

'delete query qryDataType if exists.
On Error Resume Next
db.QueryDefs.Delete "qryDataType"
On Error GoTo 0

' create and run query qryDataType.
Set qDef = db.CreateQueryDef("qryDataType", SQL)
DoCmd.OpenQuery "qryDataType"
End Sub

 

[Mile-O]

Before you run the SQL statement you could add a line with MsgBox strCriteria to see exactly how your criteria is building up so you know what bits aren't forming correctly.

I did something similar yesterday, differently but similar:

My code looks at three filter criteria on a form: a combobox and two textboxes where criteria in any of these three will filter the items shown in a listbox.

It's not the tidiest of code but it works in building the WHERE part of an SQL statement depending upon what values, if any, said controls hold. I'll need to refine it a little as it is ugly to look at...

If IsNull(cboGuarantee) Then
        strWhere = vbNullString
    Else
        strWhere = "([Guarantee Under Investigation] = '" & cboGuarantee.Column(1) & "') AND "
    End If
    
    If IsNull(txtSurname) Then
        strWhere = strWhere & vbNullString
    Else
        strWhere = strWhere & "([Customer Surname] Like '*" & txtSurname & "*') AND "
    End If
    
    If IsNull(txtPolicy) Then
        strWhere = strWhere & vbNullString
    Else
        strWhere = strWhere & "([Policy Number] Like '*" & txtPolicy & "*')"
    End If
    
    If Right(strWhere, 4) = "AND " Then
        strWhere = Left(strWhere, Len(strWhere) - 4)
    End If
    
    If strWhere = vbNullString Then
        ' do nothing
    Else
        strWhere = " WHERE " & strWhere
    End If
    
lstResults.RowSource = "SELECT DISTINCTROW tblInitialInvestigationNotes.[IIN ID], " _
        & "tblInitialInvestigationNotes.[Guarantee Under Investigation], " _
        & "tblInitialInvestigationNotes.[Customer Surname], tblInitialInvestigationNotes.[Policy Number], " _
        & "tblInitialInvestigationNotes.Position FROM tblInitialInvestigationNotes" & strWhere & ";"