Search memo field using multiple keywords in textbox

[AsjenW]

Hi folks,



I have a simple Access database where people can search for contacts. Each contact also has a memo-field where all sorts of data can be put. I'd like to search that field using a text box. When multiple terms are typed in the text box, access currently only filters the records containing those keywords in that exact order. But I'd like to see those records that contain all keywords typed in the text box, independent of their order.
E.g.: when I search for "car license", the record containing the sentence "I have got a car license" is displayed, but the record containing "I've got a license for my car" isn't.

Does anybody know how to do this? An option would be just to use multiple textboxes with one term per text box, but I hope another solution is available for just one text box.

Thanks!

[Rawb]

The only way to do this is to write a VBA function that splits your search string into individual words. Then, it just appends a "LIKE '*<WORD>*' to the SQL Query.

So, using your example, you would end up with the WHERE Clause of:

WHERE [MyField] LIKE "*car*" AND [MyField] LIKE "*license*"

[AsjenW]

That sounds like a sensible option. I'm searching for a way to append a String to the end of an SQL-query, but i cannot find a suitable method. Could you help me out?

[Rawb]

Here you go! The following code will create the appropriate WHERE condition for your SQL Query.

Public Function LikeAppend(SearchString As String, SearchField As String, Optional FindAll As Boolean = True) As String
  ' This Function returns a SQL WHERE condition to search for (multiple)
  ' strings within a Table Field.
  '
  ' SearchString - The list of strings to search for, separated by spaces.
  '      Required.
  ' SearchField - The Table Field you are searching. Multiple Fields cannot be
  '      used. If spaces or special characters are used in the Field name, you
  '      must enclose it in square brackets ([]). Required.
  ' FindAll - Whether to require all words in the search string to be found or
  '      just one. True or False. If ommitted, assumed to be True.
  '
  ' Example usage:
  '     strCriteria = LikeAppend("Search String", "[MyField]", True)
  ' Will return:
  '     strCriteria = " [MyField] LIKE '*Search*' AND [MyField] LIKE '*String*' "
  On Error GoTo Error_LikeAppend

  ' Temporary variable to hold the length of the current string
  Dim nbrIndex As Long

  ' Make sure the passed SearchString argument isn't empty
  If Len(SearchString & vbNullString) = 0 Then
    LikeAppend = ""
  Else
    ' Loop through the passed SearchString and cut out each space delimited
    ' substring
    Do While Not Len(SearchString & vbNullString) = 0
      ' Get the length of the current substring
      nbrIndex = InStr(1, SearchString, " ")

      ' If there is only one substring in the passed SearchString argument,
      ' the above will return a length of 0. If that happens, assume the entire
      ' argument is one substring and return the entire thing.
      If nbrIndex = 0 Then
        nbrIndex = Len(SearchString) + 1
      End If

      ' If this is not our first substring, be sure to append "AND" or "OR" to
      ' result to prevent a SQL Syntax error
      If Not Len(LikeAppend & vbNullString) = 0 Then
        If FindAll = True Then
          ' If we need to find ALL of the passed substrings, use "AND"
          LikeAppend = LikeAppend & " AND"
        Else
          ' If we only need to find one of the passed substrings, use "OR"
          LikeAppend = LikeAppend & " OR"
        End If
      End If

      ' Append the appropriate SQL WHERE condition to the result
      LikeAppend = LikeAppend & " " & SearchField & " LIKE '*" & Mid(SearchString, 1, nbrIndex - 1) & "*'"

      ' Remove the substring we just appended from the passed SearchString
      ' argument so we can start the loop over with the next substring
      SearchString = Mid(SearchString, nbrIndex + 1, Len(SearchString) + 1)
    Loop

    ' Make sure we aren't returning a null value
    If Len(LikeAppend & vbNullString) > 0 Then
      LikeAppend = LikeAppend & " "
    Else
      LikeAppend = ""
    End If
  End If

Function_Closing:
  ' Exit the function!
  Exit Function

Error_LikeAppend:
  ' Alert the user to the error!
  MsgBox "Unhandled error in Function LikeAppend()." & vbCrLf & vbCrLf & _
         Err.Number & ": " & Err.Description

  LikeAppend = ""

  Resume Function_Closing
End Function

[AsjenW]

Thanks a lot for the code! It works now.