Unbound text box search: multiple search terms

[dustinros]

Hi there, I've got a form that has an unbound text box where you can enter in text and it will search a field, then narrow down the findings in another text box, from which you can double click a result and associated information will pop up into 3 other unbound text boxes.

My problem is if you type in any part of a word that is in that field, it will show it - this is good. The bad part is that you cant type multiple words into the search (you can, but will only return a result if they are typed in exactly like that).

Here's a sample from the code:
_____________________________________________
Private Sub txtSearch_Change()
Dim SQL As String

Me.txtDefinition = Null

SQL = "Select [Desc] from [tblParts]" & _
" where [Desc] LIKE ""*" & Me.txtSearch.Text & "*"""

Me.lstTerms.RowSource = SQL

End Sub

Private Sub lstTerms_DblClick(Cancel As Integer)
Dim SQL As String

SQL = "Select [ROS_Part#] from [tblParts]" & _
" where [ROS_Part#]= """ & Me.lstTerms & """"

Me.txtDefinition = DLookup("[ROS_Part#]", "tblParts", _
"[Desc]=""" & Me.lstTerms & """")

Me.txtDefinition.SetFocus
__________________________________________

An example of what i'm talking about is if I have an entry and it's "Resistor 20 OHM" but I type in "20 OHM Resistor" my result wont show (it will show until after 'OHM' since that exact phrase is in the field - with 55,000 records, it's an issue).

Not sure if I even explained this right, but if anybody has any ideas, I'd love to hear 'em! Thanks!

 

[Remou]

Hi
I think you need to split your search string up and then search for each word. Something like:

Keyarray=split(Me.lstTerms, " ")
for each kword in keyarray
  sql=sql & "And [ROS_Part#] Like '*" & kword & "*'"
next
sql=mid(sql,5)
sql="Select Fields, [ROS_Part#] from tblParts Where " & sql

 

[dustinros]

Hi Remou, thanks - I just tried that but I got a variable not defined error immediately after typing in something. I changed the [ROS_Part#] to [Desc] since that is the field from tblParts I am searching.

Thanks though!