Search by Multiple Keywords

[mforema]

Hi Everyone,

I want to search records by typing in multiple keywords. I currently have a search form. It has a combo box, text box, Search command button, and a subform. The combo box lists the names of the fields found in my subform. The search form is supposed to allow a user to choose which field he/she wants to search by and then type a keyword(s) in the text box. The subform should display the filtered results.

My problem occurs when I try to use multiple keywords.

For instance, I have a field called KEYWORD in the combo box. In my table, the KEYWORD field could contain only one keyword (like "Distillation") OR it could have multiple keywords (like "Distillation, Crystalization, Vaporization") for each record. In other words, each record has either one keyword or many keywords.

I've been able to write code that searches by single keywords. I can also type in a string of keywords to get a result, BUT they have to be written in the same order exactly as they are found in the table. For instance, using my previous example, I could type in "Distillation, Crystalization" and I would get a result. But if I type "Distillation, Vaporization", I don't get any results b/c that string is not recognized.

How do I get around this problem?

My search code is found below:

Private Sub cmdSearch_Click()
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
    Else
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
 
        'Filter frmReferenceBooks based on search criteria
        Form_frmReferenceBooks.RecordSource = "select * from tblReferenceBooks where " & GCriteria
        Form_frmReferenceBooks.Caption = "tblReferenceBooks (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
 
        MsgBox "Results have been filtered."
    End If
End Sub  

Thanks!
~mforema

[NeoPa]

Try something like :

Private Sub cmdSearch_Click()
    Dim intIdx As Integer
    Dim strWork As String, strSearch As String
    Dim GCriteria As String    'Unnecessary if Dimensioned elsewhere
 
    If Nz(cboSearchField, "") = "" Then
        MsgBox "You must select a field to search."
    ElseIf Nz(txtSearchString, "") = "" Then
        MsgBox "You must enter a search string."
    Else
        'Generate search criteria
        GCriteria = ""
        strSearch = txtSearchString
        Do
            intIdx = InStr(1, strSearch, ",")
            If intIdx > 0 Then
                strWork = Trim(Left(strSearch, intIdx - 1)
                strSearch = Mid(strSearch, intIdx + 1)
            Else
                strWork = Trim(strSearch)
                strSearch = ""
            End If
            GCriteria = GCriteria & " AND ([" & cboSearchField & _
                        "] Like '*" & strWork & "*')"
        Loop While strSearch > ""
        GCriteria = Mid(GCriteria, 6)
        With Me   'Assuming Me = Form_frmReferenceBooks otherwise With Form_frmReferenceBooks
            'Filter frmReferenceBooks based on search criteria
            .RecordSource = "SELECT * " & _
                            "FROM tblReferenceBooks " & _
                            "WHERE " & GCriteria
            .Caption = "tblReferenceBooks (" & _
                       cboSearchField & _
                       " contains '" & _
                       txtSearchString & "')"
        End With
        MsgBox "Results have been filtered."
    End If
End Sub

[NeoPa]

NB. I removed the wildcards (*) from the .Caption, as that would not read very intelligently for multiple keywords.

[FishVal]

Hi Everyone,

I want to search records by typing in multiple keywords. I currently have a search form. It has a combo box, text box, Search command button, and a subform. The combo box lists the names of the fields found in my subform. The search form is supposed to allow a user to choose which field he/she wants to search by and then type a keyword(s) in the text box. The subform should display the filtered results.

My problem occurs when I try to use multiple keywords.

For instance, I have a field called KEYWORD in the combo box. In my table, the KEYWORD field could contain only one keyword (like "Distillation") OR it could have multiple keywords (like "Distillation, Crystalization, Vaporization") for each record. In other words, each record has either one keyword or many keywords.

I've been able to write code that searches by single keywords. I can also type in a string of keywords to get a result, BUT they have to be written in the same order exactly as they are found in the table. For instance, using my previous example, I could type in "Distillation, Crystalization" and I would get a result. But if I type "Distillation, Vaporization", I don't get any results b/c that string is not recognized.

How do I get around this problem?

My search code is found below:

Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
 
'Filter frmReferenceBooks based on search criteria
Form_frmReferenceBooks.RecordSource = "select * from tblReferenceBooks where " & GCriteria
Form_frmReferenceBooks.Caption = "tblReferenceBooks (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
 
MsgBox "Results have been filtered."
End If
End Sub 

Thanks!
~mforema

Hi!

As far as I've got it you want your search criteria evaluate to True if all keywords provided as comma delimited string (txtSearchString) are found in text type field no matter in what order they appear.

There are at least two ways.

1. Split txtSearchString into separate keywords and generate criteria like
InStr(,[Field],keyword1)<>0 AND InStr(,[Field],keyword1)<>0 AND ... AND InStr(,[Field],keywordN)<>0

2. Write VBA function performing desired string comparisson to use it in SQL expression.

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
ByVal varField As Variant) As Boolean
Dim intPos As Integer
Dim strKeyWord As String
 
KeyWordsInStr = False
 
Do
intPos = InStr(1, strKeyWords, ",")
If intPos = 0 Then
strKeyWord = RTrim(strKeyWords)
Else
strKeyWord = Left(strKeyWords, intPos - 1)
strKeyWords = LTrim(Mid(strKeyWords, intPos + 1))
End If
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
 
KeyWordsInStr = True
End Function
 

[mforema]

Try something like :

Private Sub cmdSearch_Click()
    Dim intIdx As Integer
    Dim strWork As String, strSearch As String
    Dim GCriteria As String    'Unnecessary if Dimensioned elsewhere
 
    If Nz(cboSearchField, "") = "" Then
        MsgBox "You must select a field to search."
    ElseIf Nz(txtSearchString, "") = "" Then
        MsgBox "You must enter a search string."
    Else
        'Generate search criteria
        GCriteria = ""
        strSearch = txtSearchString
        Do
            intIdx = InStr(1, strSearch, ",")
            If intIdx > 0 Then
                strWork = Trim(Left(strSearch, intIdx - 1)
                strSearch = Mid(strSearch, intIdx + 1)
            Else
                strWork = Trim(strSearch)
                strSearch = ""
            End If
            GCriteria = GCriteria & " AND ([" & cboSearchField & _
                        "] Like '*" & strWork & "*')"
        Loop While strSearch > ""
        GCriteria = Mid(GCriteria, 6)
        With Me   'Assuming Me = Form_frmReferenceBooks otherwise With Form_frmReferenceBooks
            'Filter frmReferenceBooks based on search criteria
            .RecordSource = "SELECT * " & _
                            "FROM tblReferenceBooks " & _
                            "WHERE " & GCriteria
            .Caption = "tblReferenceBooks (" & _
                       cboSearchField & _
                       " contains '" & _
                       txtSearchString & "')"
        End With
        MsgBox "Results have been filtered."
    End If
End Sub

Thanks! It worked perfectly!

[NeoPa]

A pleasure, and thanks for letting us know :)