CODEWITHSUNDEEP
vbams-access
Moritz
Moritz

Reputation: 389

Access dynamic filtering listbox with double where clause

I've got a dynamic textbox filtering function in VBA

Dim sSQL As String
sSQL = "SELECT qry_allUtilities.ID, qry_allUtilities.Supplier AS Lieferant, qry_allUtilities.Cabinet AS Ablageort, qry_allUtilities.Size AS Grösse, qry_allUtilities.WorkingLength AS Nutzlänge, qry_allUtilities.Description AS Bezeichnung "
sSQL = sSQL & "  FROM qry_allUtilities "
If Not sFilter = "" Then
    Dim arrFilter
    arrFilter = Split(sFilter, "+")
    Dim varWort
    For Each varWort In arrFilter
        If Not varWort = "" Then
            Dim sWort As String
            sWort = varWort
            sSQL = sSQL & " AND [ID] & ' ' & [Supplier] & ' ' & [Floor] & ' ' & [Cabinet] & ' ' & [Size] & ' ' & [WorkingLength] LIKE '*" & sWort & "*'"
        End If
    Next
    sSQL = Replace(sSQL, " AND ", " WHERE ", 1, 1, vbTextCompare)
End If
ctlListe.RowSource = sSQL

and would like to extend this with another WHERE clause because I have to exclude the records with qry_allUtilities.InActive=False

How do I do this? I always keep getting null or it won't exclude the records with InActive=True :/

Upvotes: 0

Views: 93

Answers (1)

trevor
trevor

Reputation: 267

I usually do this to add a variable (but unknown) number of filter options:

strFilter = ""    ' build the filter string in here

if <first condition reason is true> then 
    strFilter = strFilter + first condition + " AND "
end if

if <second condition reason is true> then 
    strFilter = strFilter + <second condition> + " AND "
end if 

' finish up
if len(strFilter) > 0 then    ' some critera are valid
    strFilter = Left(strFilter, Len(strFilter) - 5) ' chop off the spare " AND "
    strFilter = " WHERE " + strFilter    ' put the " WHERE " on the front
' else   ' no where clause
end if

Note that the spaces either side of the " AND " and " WHERE " are important.

Upvotes: 1

Related Questions