Search subform multiple criteria

janvk · Apr 16, 2017

Dear access users,

I was able to create search as you type form from info from your excellent site.
I use it to search through a music database. So i have a table with Artist and Title in it.

So when i type queen i show the results in a subform as i type.

But ia have multiple "Queen" records, so i would like to be able to search for The show must go on.

So in my search field i would like to be able to type Queen Show and end up with the right result.

I use a query underneath the subform where i use the : LIke "*" & searchtextbox & "*"

I use in it in a OR like statement so for each field i am searching for but when i type the second word it is still one criteria "Queen Show" so I end up with no results.

So i was trying to figure it out by using allen browns parsingword to split my search criteria in to different criteria but I cant get it to work because it is not Always one word for artist, sometimes users type "Rolling Stones Black" so 2 words for artist and 1 for Title and an other users types "Rolling Paint it black" so thats one for artist and three for title.

But if i use that and some type for example the word "the" i and up with half of the result of the table thousands of records so that doesn't work the other way.

I would like to search in one field but i have a fear i would have to use 2 fields (artist & title) but for the end-user convienience i would like stay with one searchfield

Could someone point me to the right direction ?

Uncle Gizmo · Apr 16, 2017

If I told you to search for "Rolling Stones Black" you would know that that "Rolling Stones" was the artist part of the search term and black was the Title of the search term. However the computer is not intelligent, it only has the pseudo intelligence that you provide it with in the way you design the tables and queries. To emulate human intelligence in MS Access you will probably have to test the single search text against the database of artists, returning any artists that matched. Then remove the matching words from the single search text, using the remaining words to find the title. I don't see this as being easy!

However it would be a very nice and educational project to undertake.

An alternative might be to have an advanced search button. Provide your user with a simple text box search, if the results are too numerous, then the user could click on a button to provide advanced search functionality.

I have described how to create an advanced search with a YouTube playlist. See this video:- Microsoft Access - Easy Search Criteria 1 which demonstrates the functionality you can achieve.

And this is a link to the YouTube playlist where you can start to create the code:- MS Access - Building Search Criteria

Gasman · Apr 16, 2017

If I was wishing too do this for my music, I'd probably try and incorporate some identifier to split the data for each type of search?

By this I mean 'Rolling Stones';black'

I don't think it is too much to ask a user to do?, and could be expanded for more fields?

'Rolling stones;black;lp' ???

isladogs · Apr 16, 2017

Hi

I've done almost exactly what you want already
The search box looks in 3 different fields so you could for example add the next step of searching for a particular song title as well.

Code:

 'This section builds a list of items from each word in the search box
    'The search text is split into separate words and a WHERE clause is built up from each word
    'The code looks for the search words in any of the fields Outline, SearchText, Details
    'Only active topics are listed
    'End users can only see available topics (depends on program features set in Change Constants form; SDA managers can see all topics)
    'The topic list is then restricted further depending on user job role
    'Its complicated but it works

In my case the search looks for any one of the search words added as that's what I needed.
This means adding extra words widens the search results rather than narrows it down.
See attached screengrabs.

However, you could easily adapt it to search for AND instead of OR

The code used for the search button is as follows (not all is relevant to you)

Code:

Private Sub cmdSearch_Click()

On Error GoTo Err_Handler

'This allows for updating of LstSearchText if accessed via Back/Next buttons
    If NextFlag = True Or BackFlag = True Then GoTo FlagStart:
    
    intScreenCount = 1
    SetFormConditions
    Me.Requery
    Me.txtDummy.SetFocus
    
FlagStart:
    
    If Nz(Me.txtSearch, "") = "" Then
        Me.LstSearchText.visible = False
        Me.lblSearchHeader.Caption = "No search topic entered"
        Exit Sub
    End If

    '=========================================
    'CR v4691
    'This section builds a list of items from each word in the search box
    'The search text is split into separate words and a WHERE clause is built up from each word
    'The code looks for the search words in any of the fields Outline, SearchText, Details
    'Only active topics are listed
    'End users can only see available topics (depends on program features set in Change Constants form; SDA managers can see all topics)
    'The topic list is then restricted further depending on user job role
    'Its complicated but it works
    
    'clear data for all previous searches
    strSearch = ""
    strWordList = ""
    strOutline = ""
    strSearchText = ""
    strDetails = ""
    strWhere = ""
    
    '================================
    'This filter section is used repeatedly as code builds up
    
    'code to limit access according to job role
    If GetSDAManagerStatus = True Then
        strSDAManager = "" 'topics not restricted
    Else 'hide topics where SDAManager field = True
        strSDAManager = " AND ((tblSDAHelp.SDAManager)=False)"
    End If
    
    If GetCPManagerStatus = True Then
        strCPManager = "" 'topics not restricted
    Else 'hide topics where CPManager field = True
        strCPManager = " AND ((tblSDAHelp.CPManager)=False)"
    End If
    
    If GetPastoralManagerStatus = True Then
        strPastoralManager = "" 'topics not restricted
    Else 'hide topics where Pastoral field = True
        strPastoralManager = " AND ((tblSDAHelp.Pastoral)=False)"
    End If
    
    If GetCalendarEditorStatus = True Then
        strCalendarEditor = "" 'topics not restricted
    Else 'hide topics where Pastoral field = True
        strCalendarEditor = " AND ((tblSDAHelp.CalendarEditor)=False)"
    End If
    
    'code to limit to available topics (except for SDAManagers)
    If GetSDAManagerStatus = True Then
        strAvailable = "" 'topics not restricted
    Else 'hide unavailable topics
        strAvailable = " AND ((tblSDAHelp.Available)=True)"
    End If
    
    'code to limit to active topics
    strActive = " AND ((tblSDAHelp.Active)=True))" 'extra closing bracket as it forms end of Where clause
    
    '=========================================
    'start the search
    strSearch = Me.txtSearch
    Me.txtSearchHeader = strSearch
    
    '===================================
    'filter the search text to remove words like 'and', 'or', 'the'
    'as these will occur throughout Details section and would give false results
    'CR v4699 - put space before & after each word to ensure search only done for whole word
    If InStr(1, strSearch, " and ") > 0 Then
        Do Until InStr(1, strSearch, " and ") = 0
        strSearch = Left(strSearch, InStr(1, strSearch, " and ") - 1) & Mid(strSearch, InStr(1, strSearch, " and ") + 4)
            Loop
    End If
    
    If InStr(1, strSearch, " & ") > 0 Then
        Do Until InStr(1, strSearch, " & ") = 0
        strSearch = Left(strSearch, InStr(1, strSearch, " & ") - 1) & Mid(strSearch, InStr(1, strSearch, " & ") + 2)
            Loop
    End If
    
    If InStr(1, strSearch, " the ") > 0 Then
        Do Until InStr(1, strSearch, " the ") = 0
        strSearch = Left(strSearch, InStr(1, strSearch, " the ") - 1) & Mid(strSearch, InStr(1, strSearch, " the ") + 4)
            Loop
    End If
    
    If InStr(1, strSearch, " or ") > 0 Then
        Do Until InStr(1, strSearch, " or ") = 0
        strSearch = Left(strSearch, InStr(1, strSearch, " or ") - 1) & Mid(strSearch, InStr(1, strSearch, " or ") + 3)
            Loop
    End If
    
    If InStr(1, strSearch, " of ") > 0 Then
        Do Until InStr(1, strSearch, " of ") = 0
        strSearch = Left(strSearch, InStr(1, strSearch, " of ") - 1) & Mid(strSearch, InStr(1, strSearch, " of ") + 3)
            Loop
    End If
    
    If InStr(1, strSearch, " a ") > 0 Then
        Do Until InStr(1, strSearch, " a ") = 0
        strSearch = Left(strSearch, InStr(1, strSearch, " a ") - 1) & Mid(strSearch, InStr(1, strSearch, " a ") + 2)
        Loop
    End If
    
    If InStr(1, strSearch, " an ") > 0 Then
        Do Until InStr(1, strSearch, " an ") = 0
        strSearch = Left(strSearch, InStr(1, strSearch, " an ") - 1) & Mid(strSearch, InStr(1, strSearch, " an ") + 3)
        Loop
    End If
    
    'Debug.Print strSearch
    
    If strSearch = "" Then
        Me.txtSearchHeader = strSearch
        Me.LstSearchText.visible = False
        Me.lblSearchHeader.Caption = "Common words like 'and', 'or' were removed from the search results" & _
            " as these can give false results. Please omit these words from your search"
        Me.lblSearchHeader.ForeColor = vbRed
        Exit Sub
    End If
    
    'restore normal colour
    Me.lblSearchHeader.ForeColor = ColDarkBlue
    
    '========================================
    'split the search text into separate words and manage each separately
    strWordList = strSearch
    
    Do Until strWordList = ""
    If strWordList > "" Then
        If InStr(1, strWordList, " ") = 0 Then 'only 1 word in list
            GoTo Done 'this deals with final (or only) word in list
            Else
                'search each word separately
                strSearch = Left(strWordList, InStr(1, strWordList, " ") - 1)
                strWordList = Mid(strWordList, InStr(1, strWordList, " ") + 1)
        
    '=====================================================
    'build up strWhere clause
    
    'If there is more than one word in search list the code loops to search each word separately adding filters from filter section
                If strOutline = "" Then
                    strOutline = "(((tblSDAHelp.Outline) Like '*' & '" & strSearch & "' & '*')"
                Else
                    strOutline = strOutline & " OR (((tblSDAHelp.Outline) Like '*' & '" & strSearch & "' & '*')"
                End If
                
                If strSearchText = "" Then
                    strSearchText = "(((tblSDAHelp.SearchText) Like '*' & '" & strSearch & "' & '*')"
                Else
                    strSearchText = strSearchText & " OR (((tblSDAHelp.SearchText) Like '*' & '" & strSearch & "' & '*')"
                End If
                    
                If strDetails = "" Then
                    strDetails = "(((tblSDAHelp.Details) Like '*' & '" & strSearch & "' & '*') AND ((tblSDAHelp.Details) Not Like '*Dummy text*')"
                Else
                    strDetails = strDetails & " OR (((tblSDAHelp.Details) Like '*' & '" & strSearch & "' & '*') AND ((tblSDAHelp.Details) Not Like '*Dummy text*')"
                End If
        End If
    End If
    
    
    strOutline = strOutline & strSDAManager & strCPManager & strCalendarEditor & strPastoralManager & strAvailable & strActive
    strSearchText = strSearchText & strSDAManager & strCPManager & strCalendarEditor & strPastoralManager & strAvailable & strActive
    strDetails = strDetails & strSDAManager & strCPManager & strCalendarEditor & strPastoralManager & strAvailable & strActive
    
    Loop
    
Done:
    'now add a WHERE clause for the final word in the list - modified v4690
        
        If strOutline = "" Then
            strOutline = "(((tblSDAHelp.Outline) Like '*' & '" & strWordList & "' & '*')"
        Else
            strOutline = strOutline & " OR (((tblSDAHelp.Outline) Like '*' & '" & strWordList & "' & '*')"
        End If
        
        If strSearchText = "" Then
            strSearchText = "(((tblSDAHelp.SearchText) Like '*' & '" & strWordList & "' & '*')"
        Else
            strSearchText = strSearchText & " OR (((tblSDAHelp.SearchText) Like '*' & '" & strWordList & "' & '*')"
        End If
            
        If strDetails = "" Then
            strDetails = "(((tblSDAHelp.Details) Like '*' & '" & strWordList & "' & '*') AND ((tblSDAHelp.Details) Not Like '*Dummy text*')"
        Else
            strDetails = strDetails & " OR (((tblSDAHelp.Details) Like '*' & '" & strWordList & "' & '*') AND ((tblSDAHelp.Details) Not Like '*Dummy text*')"
        End If
        
    strOutline = strOutline & strSDAManager & strCPManager & strCalendarEditor & strPastoralManager & strAvailable & strActive
    strSearchText = strSearchText & strSDAManager & strCPManager & strCalendarEditor & strPastoralManager & strAvailable & strActive
    strDetails = strDetails & strSDAManager & strCPManager & strCalendarEditor & strPastoralManager & strAvailable & strActive
    
    strSelect = "SELECT tblSDAHelp.ID, tblSDAHelp.Outline, tblSDAHelp.SearchText, tblSDAHelp.Details FROM tblSDAHelp"
    strOrderBy = " ORDER BY tblSDAHelp.SearchText;"
    
    strWhere = " WHERE " & strOutline & " OR " & strSearchText & " OR " & strDetails
    strSQL1 = strSelect & strWhere & strOrderBy
    'Debug.Print strSQL1
    Me.LstSearchText.RowSource = strSQL1
    
    '=====================================================
    Me.LstSearchText.ColumnWidths = "0cm;0cm;4cm;0cm;0cm"
    
    If Me.LstSearchText.ListCount > 1 Then
        Me.LstSearchText.visible = True
        Me.LstSearchText = ""
        Me.lblSearchHeader.Caption = Me.LstSearchText.ListCount & " results for: "
        Me.LblTryBrowse.visible = False
    ElseIf Me.LstSearchText.ListCount = 1 Then
        Me.LstSearchText.visible = True
        Me.LstSearchText = ""
        Me.lblSearchHeader.Caption = Me.LstSearchText.ListCount & " result for: "
        Me.LblTryBrowse.visible = False
    Else
        Me.LstSearchText.visible = False
        Me.lblSearchHeader.Caption = "No results for: "
        Me.LblTryBrowse.visible = True
    End If
    
    Me.lblBrowse.Caption = ""
    
    If NextFlag = True Or BackFlag = True Then Exit Sub
    'else add user log record to tblSDAHelpUserHistoryTEMP
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO tblSDAHelpUserHistoryTEMP ( IntScreenCount, ScreenType, TeacherID, EventTime, SDAHelpID, Outline, SearchText, MoreDetail, HelpFile )" & _
            " SELECT DISTINCTROW 1 AS IntScreenCount, 'Search Results' AS ScreenType, GetLoggedOnTeacher() AS TeacherID, Now() AS EventTime," & _
            " 0 As SDAHelpID, '' AS Outline, '" & strSearch & "' AS SearchText, 0 AS MoreDetail, '' AS HelpFile;"
        DoCmd.SetWarnings True
        
        'save initial record ID for later use
        Me.txtUserLogID = Nz(DMax("ID", "tblSDAHelpUserHistoryTEMP"), 0)

Exit_Handler:
    Exit Sub

Err_Handler:
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    PopulateErrorLog
    Resume Exit_Handler

End Sub

If you think this is potentially useful to your needs, you are free to adapt it.
Hopefully there are enough comments to help you make sense of it.

Happy to explain further if needed

Uncle Gizmo · Apr 18, 2017

An OP has just now asked a very similar question. I have linked the questions together as you may benefit from the answers the other user received. The thread is Here:- https://www.access-programmers.co.uk/forums/showthread.php?t=293236

Search subform multiple criteria

janvk

New member

Uncle Gizmo

Nifty Access Guy

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Attachments

Uncle Gizmo

Nifty Access Guy

Similar threads

Users who are viewing this thread