Expading a strfilter search Parameter Code based on extra criteria in this case a choice list

[June7]

Check the ControlSource property of controls used for filtering. Should be blank.



Don't know your db nor code so don't know why multi-filter not working.

[ThunderSpark]

Check the ControlSource property of controls used for filtering. Should be blank.

Don't know your db nor code so don't know why multi-filter not working.

Thanks that solved it, I will give you the code again that does the searching, let me know if you can see the error why multifultering does not seem to work:

Private Sub Knop12_Click()
    Dim strSQL      As String
    Dim strFilter   As String
    Dim rstX        As DAO.Recordset
    Dim strKeywords As String
    Dim blnFilter   As Boolean
    Dim i           As Integer
    Dim y           As Variant
    Dim strValue1   As String
    Dim strValue2   As String
    Dim strValue3   As String
    Dim strValue4   As String
    Dim strValue5   As String
    Dim strValue6   As String
    
    On Error GoTo Err_Knop12_Click

    blnFilter = False
    strFilter = "1=1"
    
    If chkKeyword.Value = 0 And LenB(txtKeyWords.Value) > 0 Then
        strFilter = strFilter & "ID IN (SELECT ID_ARTICLE FROM ARTICLE_KEYWORDS WHERE ID_KEYWORD IN (select ID FROM KEYWORDS WHERE FILTER_CHECK = TRUE))"
        blnFilter = True
    End If
    
    If LenB(txtArticleName.Value) > 0 And chkArticleName.Value <> 0 Then
        y = Split(LCase$(txtArticleName.Value), " ")
        'strValue = vbn
        'For i = LBound(y) To UBound(y)
            ' https://stackoverflow.com/questions/19780016/vba-write-all-possible-combinations-of-4-columns-of-data
        '    strValue = strValue & y(i)
        'Next i
        Select Case UBound(y)
            Case 1
                strValue1 = LCase$(Replace$(txtArticleName.Value, " ", "*"))
                strValue2 = y(1) & "*" & y(0)
                strFilter = strFilter & "AND (ARTICLE_NAME LIKE '*" & strValue1 & "*' OR ARTICLE_PATH LIKE '*" & strValue1 & _
                    "*' OR ARTICLE_NAME LIKE '*" & strValue2 & "*' OR ARTICLE_PATH LIKE '*" & strValue2 & "*')"
            Case 2
                strValue1 = LCase$(Replace$(txtArticleName.Value, " ", "*"))
                strValue2 = y(2) & "*" & y(1) & "*" & y(0)
                strValue3 = y(2) & "*" & y(0) & "*" & y(1)
                strValue4 = y(1) & "*" & y(2) & "*" & y(0)
                strValue5 = y(1) & "*" & y(0) & "*" & y(2)
                strValue6 = y(0) & "*" & y(2) & "*" & y(1)
                strFilter = strFilter & "AND (ARTICLE_NAME LIKE '*" & strValue1 & "*' OR ARTICLE_PATH LIKE '*" & strValue1 & _
                    "*' OR ARTICLE_NAME LIKE '*" & strValue2 & "*' OR ARTICLE_PATH LIKE '*" & strValue2 & _
                    "*' OR ARTICLE_NAME LIKE '*" & strValue3 & "*' OR ARTICLE_PATH LIKE '*" & strValue3 & _
                    "*' OR ARTICLE_NAME LIKE '*" & strValue4 & "*' OR ARTICLE_PATH LIKE '*" & strValue4 & _
                    "*' OR ARTICLE_NAME LIKE '*" & strValue5 & "*' OR ARTICLE_PATH LIKE '*" & strValue5 & _
                    "*' OR ARTICLE_NAME LIKE '*" & strValue6 & "*' OR ARTICLE_PATH LIKE '*" & strValue6 & _
                    "*')"
            Case Else
                strFilter = strFilter & "AND (ARTICLE_NAME LIKE '*" & LCase$(Replace$(txtArticleName.Value, " ", "*")) & _
                    "*' OR ARTICLE_PATH LIKE '*" & LCase$(Replace$(txtArticleName.Value, " ", "*")) & "*')"
        End Select
        blnFilter = True
    End If
    If txtBestandExtensie > 0 And chkBestandExtensie <> 0 Then
    strFilter = "ARTICLE_NAME Like '*" & Me.txtBestandExtensie & "*'"
    blnFilter = True
    End If
    
    If chkBib.Value = 0 Then
        strFilter = strFilter & " AND ARTICLE_PATH NOT LIKE 'BIB (*'"
        blnFilter = True
    End If
    
    If chkBibGem.Value = 0 Then
        strFilter = strFilter & " AND ARTICLE_PATH NOT LIKE 'GEM (*'"
        blnFilter = True
    End If
    
    Set rstX = CurrentDb.OpenRecordset("SELECT FOLDER_PATH FROM ADMIN_FOLDERS WHERE FILTER_CHECK2 = FALSE")
    With rstX
        While Not .EOF
            If .Fields("FOLDER_PATH") = getParam("FTN_ROOT_DIR") Then
                strFilter = strFilter & " AND ARTICLE_PATH NOT LIKE '*" & .Fields("FOLDER_PATH") & "*' AND ARTICLE_PATH NOT LIKE 'MAG (*' AND ARTICLE_PATH NOT LIKE '*PUBLICATIES_SCANS*'"
            Else
                strFilter = strFilter & " AND ARTICLE_PATH NOT LIKE '*" & .Fields("FOLDER_PATH") & "*'"
            End If
            blnFilter = True
            .MoveNext
        Wend
        .Close
    End With
    
    If blnFilter Then
        Me.FRM_ARTICLES_2.Form.Filter = strFilter
        gstrFilter = Me.FRM_ARTICLES_2.Form.Filter
        Me.FRM_ARTICLES_2.Form.FilterOn = True
    Else
        Me.FRM_ARTICLES_2.Form.FilterOn = False
    End If
    
Exit_Knop12_Click:
    Exit Sub

Err_Knop12_Click:
    MsgBox Err.Description
    Resume Exit_Knop12_Click
    
End Sub

[June7]

Your code is too complicated for me to analyze visually. I would have to interact with db.

Have you step debugged? Debug.Print strFilter so you can check constructed string for syntax errors.

I thought you wanted to avoid structure that limits the number of elements.

[ThunderSpark]

Your code is too complicated for me to analyze visually. I would have to interact with db.

Have you step debugged? Debug.Print strFilter so you can check constructed string for syntax errors.

I thought you wanted to avoid structure that limits the number of elements.

I did some experiment and I solved the problem I switched that part of searching by file extension higher up before the search by text goes and it is kinda solved.


No I did not wanted to limit the number of elements in the search. This a doc manager database and there were two specific types of users.

Also while I am currently on this database, the code that was already there was not written by me.

I got an assignment to make sure the database could work specifically for library, therefore hiding and enabling/disabling certain features by default and to make searching more specific.
I used the code that was already there as a basis and modified it.