multiple keyword search

[Guest]

I set up a database with a table that contains a field with keywords. I have
a form where the user can enter up to three keywords and then select search
to return all records attached to the keywords. I know how to create a
simple paramater query using one keyword search, but I would like the user to
be able to enter up to 3 keywords. I did this once before (with a
programmer) and I think it involved quite a bit of code. Can anyone get me
started? Thanks for your time.

Holly

 

[Allen Browne]

I presume you want to be able to enter all the keywords into one text box,
and build a Filter for your form that returns the records where the Notes
fields (for example) contains ony of the keywords:

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) >= 99 Then '99 words max.
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 & "([Notes] 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

 

[Guest]

Thanks Allen,
Yes, currently I have a form set up with 3 places the user can enter their
keywords and a search button that activates the code when it is clicked. I
have some basic code I am referencing, and along with your code, it gets me
closer to the solution.
I am fairly new at learning Visual Basic Code, it's kind of like learning
Latin.
Thanks again.
--
Holly

I presume you want to be able to enter all the keywords into one text box,
and build a Filter for your form that returns the records where the Notes
fields (for example) contains ony of the keywords:

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) >= 99 Then '99 words max.
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 & "([Notes] 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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I set up a database with a table that contains a field with keywords. I
have
a form where the user can enter up to three keywords and then select
search
to return all records attached to the keywords. I know how to create a
simple paramater query using one keyword search, but I would like the user
to
be able to enter up to 3 keywords. I did this once before (with a
programmer) and I think it involved quite a bit of code. Can anyone get
me
started? Thanks for your time.

Holly

 

[Guest]

Hi Allen,
Your code is very good. I also have a similar question as Holly but how
should I change your code to fit my need? Rather 3 keywords, I want to search
with 2 key words in different text box and the record result must have the 2
keywords, no either or. Thanks. Look forward to your reply.

esca007

I presume you want to be able to enter all the keywords into one text box,
and build a Filter for your form that returns the records where the Notes
fields (for example) contains ony of the keywords:

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) >= 99 Then '99 words max.
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 & "([Notes] 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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I set up a database with a table that contains a field with keywords. I
have
a form where the user can enter up to three keywords and then select
search
to return all records attached to the keywords. I know how to create a
simple paramater query using one keyword search, but I would like the user
to
be able to enter up to 3 keywords. I did this once before (with a
programmer) and I think it involved quite a bit of code. Can anyone get
me
started? Thanks for your time.

Holly

 

[Mary]

Hi Mr. Browne:

This looks like an exciting way to enhance our database. However, I am not
sure where to put this code. If you have the time and inclination, would you
be willing to dumb this down a bit for a newbie? I have the following things
in place:
A table with fields for titles and for keywords.
A query that at this point only has the title and keyword fields, but shows
all records when executed.
A form with a text box for entering a keyword and an "OK" button that will
eventually search for the keyword when the button is clicked.

But I am not sure how to set this up... where to put your code...

I presume you want to be able to enter all the keywords into one text box,
and build a Filter for your form that returns the records where the Notes
fields (for example) contains ony of the keywords:

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) >= 99 Then '99 words max.
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 & "([Notes] 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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I set up a database with a table that contains a field with keywords. I
have
a form where the user can enter up to three keywords and then select
search
to return all records attached to the keywords. I know how to create a
simple paramater query using one keyword search, but I would like the user
to
be able to enter up to 3 keywords. I did this once before (with a
programmer) and I think it involved quite a bit of code. Can anyone get
me
started? Thanks for your time.

Holly