Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32

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

    Hello, Sorry for Posting another topic about my same (docmanager) database, but this is a seperate problem I need some help with. This database so far has some code written to search for articles based on text. And based on other parameters.

    There s code that has already been written so I should need to use this code as basic. But it is kind of a head scratcher:

    Code:
    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 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

    The code that is in bold is the code that represent when certain text has been searched however this is the code I actually want to expand.

    Want I want to do is making the searching more specific based on what file extension is in the text.
    So this code parameter:
    Code:
    If LenB(txtArticleName.Value) > 0 And chkArticleName.Value <> 0 Then
    Should be:
    Code:
    (txtbestandextensie.Value) > 0 and chkbestandextensie.Value  <> 0 Then



    Now this choice list has a querry as a basis for the selection and what has to happen is the following:

    Code:
    Search like "File Extension choice"
    so for example when I select the .pdf extension what actually has to happen is: Search like ".pdf".

    Code:
     ARTICLE_NAME LIKE ".pdf"
    Here is the Querry and how it looks like in the form.

    Querry:

    Click image for larger version. 

Name:	Querry 4.png 
Views:	20 
Size:	9.1 KB 
ID:	38761


    Form:
    Click image for larger version. 

Name:	Search Forum.png 
Views:	20 
Size:	53.9 KB 
ID:	38762




    Also small side note: For some unknown reason the top row also gets deleted "sometimes" when I boot up the database: why this happens I have no clue: So if someone can explain and point out why this is. Let me know.

    Click image for larger version. 

Name:	Querry 4 Top Row gets deleted.png 
Views:	19 
Size:	9.4 KB 
ID:	38763

    So what I know so far is that the I need to use the strFilter but so far I do not know how to write this based on a list.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to select multiple items and build filter criteria that would be an array list to find records that match on any item? Usually this is accomplished with a multi-select listbox to build parameter like:

    WHERE somefield IN(item1, item2, item3, etc)

    Review http://allenbrowne.com/ser-50.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    Quote Originally Posted by June7 View Post
    You want to select multiple items and build filter criteria that would be an array list to find records that match on any item? Usually this is accomplished with a multi-select listbox to build parameter like:

    WHERE somefield IN(item1, item2, item3, etc)

    Review http://allenbrowne.com/ser-50.html
    Not Quite with multiple item, just the one selected from the list to make things simple. Because people that are searching just want to search for that file type or at least an Article Name with that Filetype.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then I don't understand the issue.

    If you only want to use single parameter selected in combobox:

    "somefield LIKE '*" & Me.combobox & "*'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    So it should be like:
    Code:
    If LenB(txtArticleName.Value) > 0 And chkArticleName.Value <> 0 Then
    "Bestand_EXT LIKE '*" & txtbestandextensie.combobox & "*'
    "

    Or am I wrong here?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, you don't use combobox unless that really is name of your combobox.
    Use actual combobox name.
    Your combobox is named txtbestandextensie?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    Yes that is correct.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, you are referencing wrong field to apply filter criteria.

    "Artikel LIKE '*" & Me.txtbestandextensie & "*'"

    Or is the field name actually ARTICLE_NAME ?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    Quote Originally Posted by June7 View Post
    Well, you are referencing wrong field to apply filter criteria.

    "Artikel LIKE '*" & Me.txtbestandextensie & "*'"

    Or is the field name actually ARTICLE_NAME ?
    The field name is indeed ARTICLE_NAME, there is also the field ARTICLE_PATH but that will not be used.
    :
    Edit
    So far I added this to the code:

    Code:
            If txtBestandExtensie > 0 And chkBestandExtensie <> 0 Then
        strFilter = ARTICLE_NAME Like "'*" & Me.txtBestandExtensie & "*'"
        End If

    No results when I press the Filter button to make the search. There does not also seem to be come up an error.

    And yes it is called txtBestandExtensie and not regular txtbestandextensie. Before you ask why I use that chkbestandextensie that is because of the following code:

    Code:
    Private Sub chkBestandExtensie_Click()
        If chkBestandExtensie Then
        txtBestandExtensie.Enabled = True
        Else
        txtBestandExtensie.Enabled = False
        txtBestandExtensie = vbNullString
        End If
    Last edited by ThunderSpark; 06-18-2019 at 05:08 AM. Reason: Some new missing information that needed to be added.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Quote marks are wrong. Try:

    strFilter = "ARTICLE_NAME Like '*" & Me.txtBestandExtensie & "*'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    Quote Originally Posted by June7 View Post
    Quote marks are wrong. Try:

    strFilter = "ARTICLE_NAME Like '*" & Me.txtBestandExtensie & "*'"
    Oke that works but the results I get are 0. While no matter what choice I set up. This should not be the case. I had to add BLN filter also so that the filter option would work even if the text was zero.

    Code:
    If txtBestandExtensie > 0 And chkBestandExtensie <> 0 Then
    strFilter = "ARTICLE_NAME Like '*" & "Me.txtBestandExtensie & *'"
    blnFilter = True
    End If

    Edit: Added Pictures

    Click image for larger version. 

Name:	Results.png 
Views:	11 
Size:	151.6 KB 
ID:	38780
    Click image for larger version. 

Name:	Results 2.png 
Views:	10 
Size:	139.6 KB 
ID:	38781

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Keep putting quote mark in wrong place.

    This

    strFilter = "ARTICLE_NAME Like '*" & "Me.txtBestandExtensie & *'"

    should be

    strFilter = "ARTICLE_NAME Like '*" & Me.txtBestandExtensie & "*'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    Quote Originally Posted by June7 View Post
    Keep putting quote mark in wrong place.

    This

    strFilter = "ARTICLE_NAME Like '*" & "Me.txtBestandExtensie & *'"

    should be

    strFilter = "ARTICLE_NAME Like '*" & Me.txtBestandExtensie & "*'"
    Ok thank you that solved the problem.

    Now that side Problem I have of the top value in the field Bestand Ext (.com) getting deleted is the next problem to solve. Any Idea of why this happens?

    Also some extra details, when that value is empty and I select another value that vallue I select is getting filled in by in that empty field.
    For Example when the .com is empty and I select . doc after that .doc is filled in that empty field. How do I prevent this?

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Controls used to select filter criteria must be UNBOUND, otherwise you change data in record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32
    Quote Originally Posted by June7 View Post
    Controls used to select filter criteria must be UNBOUND, otherwise you change data in record.
    Sorry if I am total noob here but how and where exacly do you change that? I am working in dutch here.

    Edit: Ok while the code seems to work so far there is another problem I have encountered while searching for text and keyword searching it seems not all filters are being applied. To narrow down the result:

    Click image for larger version. 

Name:	Not all Filters are applied.png 
Views:	9 
Size:	145.2 KB 
ID:	38785
    So while it does search by File Extension it does not account for all the other filters applied

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-14-2017, 02:24 AM
  2. conditional strFilter formatting/code
    By lbaccess101 in forum Reports
    Replies: 6
    Last Post: 08-07-2017, 01:17 PM
  3. Replies: 0
    Last Post: 03-19-2017, 01:29 PM
  4. Replies: 3
    Last Post: 02-26-2016, 09:15 AM
  5. Replies: 3
    Last Post: 01-04-2015, 06:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums