Search 3 Fields in one form, from one text box.

  • Thread starter calum112 via AccessMonster.com
  • Start date
C

calum112 via AccessMonster.com

This has come up several times so I applogise in advance, I have however read
all of the threads I can find from searching and am still stuck :( .

I have a text box in the footer of a form with a command button that I would
like to use as a search function. I would like it to search three fields in
that same form and bring back any record where any of the words / numerics
appear in any of those fields.

The fields are called:
Make/Model
Serial No
Description

The search text box is called:
SearchMakeDescription

For instance I could search "hp 20937", "hp" could appear in the Make/Model
field and "20937" could appear in the Searial No field, so I woudl want that
record.
 
C

calum112 via AccessMonster.com

Oh:

I was thinking I coudl maybe use a query to merge the three fields, the
search can then search those fields as one and bring back the record using
it's primary key fields.

Much appreciated :)
 
A

Allen Browne

There are two parts to your question:
- how to parse the words typed into the text box;
- how to apply those words against multiple fields.

The example below assumes you have a bound form to show the results, and a
text box named txtKeywords where the user types in the keywords to find. The
code goes into the AfterUpdate event procedure of the text box. It uses the
Split() function to parse the words from the text box into an array, and
then builds a string (strWhere) to apply to the Filter of the form. Since
the words can appear anywhere in the field, you must use the Like operator
with wildcards (not the IN operator), so it can handle up to 33 words. Trust
you can adapt that to suit.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Make/Model] Like ""*" & strWord
& _
"*"") OR ([Serial No] Like ""*" & strWord & _
"*"") OR ([Description] Like ""*" & strWord & "*"")
OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
 
Top