Here is the RequeryList sub covered in the Search Seminar:
Private Sub RequeryList()
Dim SQL As String, Wh As String
Dim S As String
Wh = ""
If IsNull(SearchTerm) Then SearchTerm = ""
S = Trim(SearchTerm) 'access dlookup table
If Left(S, 1) = "[" Then ' exact search
S = Replace(S, "[", "") ' [about database]
S = Replace(S, "]", "") ' [about database]
Wh = "CourseName = """ & S & """ OR Description = """ & S & """"
ElseIf Left(S, 1) = """" Then ' whole phrase search
S = Replace(S, """", "") ' "about database"
Wh = "CourseName LIKE ""*" & S & "*"" OR Description LIKE ""*" & _
S & "*"""
ElseIf InStr(S, " ") <> 0 Then ' have spaces
Wh = "(CourseName LIKE ""*"
Wh = Wh & Replace(S, " ", "*"" OR CourseName LIKE ""*")
Wh = Wh & "*"") OR ("
Wh = Wh & "Description LIKE ""*"
Wh = Wh & Replace(S, " ", "*"" OR Description LIKE ""*")
Wh = Wh & "*"") "
' look for negative keywords
Wh = Replace(Wh, " OR CourseName LIKE ""*-", " AND CourseName NOT LIKE ""*")
Wh = Replace(Wh, " OR Description LIKE ""*-", " AND Description NOT LIKE ""*")
Wh = Replace(Wh, " LIKE ""*-", " NOT LIKE ""*")
If InStr(Wh, "NOT LIKE") <> 0 Then
Wh = Replace(Wh, ") OR (", ") AND (")
End If
Else ' no space
Wh = "CourseName LIKE ""*" & S & "*"" OR Description LIKE ""*" & _
S & "*"""
End If
Wh2 = Wh
If Wh <> "" Then Wh = "WHERE " & Wh
SQL = "SELECT CourseID, CourseName FROM CourseT " & Wh
SQL = SQL & " ORDER BY CourseName"
MySQL = SQL
CourseList.RowSource = SQL
End Sub
