Search separate terms in a form

[Guest]

I created a unbound textbox to search one of the tables, it works well for
one search.
But the problem is usually, users would type in more than one search terms,
and I would like to make it possible to retrieve records if any of the search
term matches.

For example, if I have a name record, "Thomas Smith", for now, it is okay
for me to type in "Thomas" or "Smith" to get the record.

What I want is if users type in "Thomas Johnson" or "Joe Smith", they can
still retrieve the record "Thomas Smith". Is it possible to do that?

My search code has been:
Like "*" & Me.[textbox] & "*"

Thanks a lot!

 

[Jeff L]

You cannot do this with a simple query. You will need some coding to
do it. Here is something I have used.

Dim FilterString As String, Position As Integer, DescriptionFilter As
String, HoldDescriptionFilter As String

If Not IsNull(Me.FilterDescription) Then
HoldDescriptionFilter = Me.FilterDescription
Position = InStr(1, HoldDescriptionFilter, ",",
vbTextCompare)
If Position > 0 Then 'There is more than one word to look
for in the description
Do Until Position = 0
DescriptionFilter = IIf(DescriptionFilter = "",
"Description like '*" & _
Left(HoldDescriptionFilter, Position - 1) & "*' ",
DescriptionFilter & "Or Description like '*" & _
Left(HoldDescriptionFilter, Position - 1) & "*' ")
HoldDescriptionFilter = Trim(Mid(HoldDescriptionFilter,
Position + 1))
Position = InStr(1, ",", HoldDescriptionFilter,
vbTextCompare)
Loop
DescriptionFilter = DescriptionFilter & "Or Description
like '*" & HoldDescriptionFilter & "%' "
FilterString = IIf(FilterString = "", DescriptionFilter, _
FilterString & "And (" &
DescriptionFilter & ")")
Else

FilterString = IIf(FilterString = "", "Description Like '*"
& Me.FilterDescription & "*'", _
FilterString & "And Description Like '*" &
Me.FilterDescription & "*'")
End IF
Me.Filter = FilterString
Me.FilterOn = True

Else
Me.FilterOn = False
End IF

 

[Guest]

Thanks very much!
I will have a try on this!