ASP with Access DB Search 1

[humbletechie]

Hi,

I am attempting to use the LIKE search function but it only works in certain cases. For example: If I search for Garcia it displays all results with the last name of Garcia, but if I type Judge Garcia it displays nothing. The first name in the DB is not judge but is there a way to display the Garcia results anyway? Below is my SQL statement.

&quot;SELECT Atty_2_CaseNew.Short_Case, Cases.[Case No], Cases.Court, Atty_2_CaseNew.[Plntf/Def], Atty_2_CaseNew.Judge, Cases.Status, Atty_2_CaseNew.Attorney FROM Cases RIGHT JOIN Atty_2_CaseNew ON Cases.[Case ID] = Atty_2_CaseNew.[Case ID] WHERE Atty_2_CaseNew.Atty_ID Is Not Null AND Atty_2_CaseNew.Judge LIKE '%&quot;&SearchTerms&&quot;%' AND Cases.[File Date] >= #&quot;&StartDate&&quot;# AND Cases.[File Date] <= #&quot;&EndDate&&quot;#&quot;

 

[Tarwn]

You could split the search text on the space character and add a portion to your sql with the like for every item in the new array:

Dim myArray
myArray = Split(SearchTerms," ")

Dim sqlStmt
sqlStmt = "SELECT Atty_2_CaseNew.Short_Case, Cases.[Case No], Cases.Court, Atty_2_CaseNew.[Plntf/Def], Atty_2_CaseNew.Judge, Cases.Status, Atty_2_CaseNew.Attorney FROM Cases RIGHT JOIN Atty_2_CaseNew ON Cases.[Case ID] = Atty_2_CaseNew.[Case ID] WHERE Atty_2_CaseNew.Atty_ID Is Not Null"
If UBound(myArray) > 0 Then   'if there were more than 1 word
   sqlStmt = sqlStmt & " AND ("
   Dim i
   sqlStmt = sqlStmt & "Atty_2_CaseNew.Judge LIKE '%"&myArray(0)&"%'" 
   For i = 1 to UBound(myArray)
      sqlStmt = sqlStmt & " OR Atty_2_CaseNew.Judge LIKE '%"&myArray(i)&"%'" 
   Next
   sqlStmt = sqlStmt & ")"
End If
sqlStmt = sqlStmt & &quot; AND Cases.[File Date] >= #&quot;&StartDate&&quot;# AND Cases.[File Date] <= #&quot;&EndDate&&quot;#&quot;

This will build a section inside your statement that says that the db record has to be LIKE only one of the words in the searchTerms instead of all of them.
Hope that helps,
-Tarwn &quot;The problem with a kludge is eventually you're going to have to back and do it right.&quot; - Programmers Saying (The Wiz Biz - Rick Cook)
&quot;Your a geek!&quot; - My Girlfriends saying

 

[humbletechie]

Thank you VERY much Tarwn. This gave me exactly what I needed.