Multiselect and ADO filter syntax problem

[thread]

Hi All,
i'm finding difficlties to build dynamic filter for ADO object when
having several of list boxes.
i dont see other option beside declaring high number of types and
looping all list boxes control several times
does anyone have an example of application with several of list boxes
when there is alot of multi-picks that needs to be the criteria for
ADO object?

 

[Douglas J. Steele]

I'm not sure I understand what ADO has to do with it.

Let's assume you've got two list boxes lstBox1 and lstBox2 which correspond
to AccountID (a numeric field) and SalesRep (a text field) respectively.

Dim strWhere As String
Dim strCond1 As String
Dim strCond2 As String
Dim varSelected As Variant

strCond1 = vbNullString
strCond2 = vbNullString
If Me.lstBox1.ItemsSelected.Count > 0 Then
For Each varSelected in Me.lstBox1.ItemsSelected
strCond1 = strCond1 & Me.lstBox1.ItemData(varSelected) & ", "
Next varSelected
strCond1 = "AccountID IN (" & Left$(strCond1, Len(strCond1) - 2) & ") "
End If
If Me.lstBox2.ItemsSelected.Count > 0 Then
For Each varSelected in Me.lstBox2.ItemsSelected
strCond2 = strCond2 & Chr$(34) & Me.lstBox1.ItemData(varSelected) &
Chr$(34) & ", "
Next varSelected
strCond2 = "SalesRep IN (" & Left$(strCond2, Len(strCond2) - 2) & ") "
End If
If Len(strCond1) > 0 Then
strWhere = "WHERE " & strCond1
End If
If Len(strCond2) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
Else
strWhere = "WHERE "
End If
strWhere = strWhere & strCond2
End If

(Actually, if you're using it as a filter, you don't need the word WHERE
there, so the last bit of code would be:

strWhere = strCond1
If Len(strCond2) > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & strCond2
End If