Find multiple words in a memo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a repair database that has a memo field. I would like users to find
any or all words from a single input box possibly seperated by commas for
example: motor, electrical, etc.

I can find any single word by using: Like "*" & [Enter word] & "*"
 
Not easily.

What you could do is have a form with a text box on it and a command button
that runs the query. In the text box's AfterUpdate event, put code to parse
how many different values are in the text box, and modify the query's WHERE
clause from

WHERE MyField LIKE "*" & [Enter word] & "*"

to

WHERE MyField LIKE "*" & word1 & "*"
OR MyField LIKE "*" & word2 & "*"
OR MyField LIKE "*" & word3 & "*"

Post back if you'd like more help with this approach.
 
What I am hoping for is more like a google search where you can enter
multiple words in 1 box and it will search the contents of the memo for any
or all words.


I don't want to open multiple boxes because the search is already part of a
form
that has 2 other fields. The search will return all results of a given field
if left blank.
see code below:

SELECT Main.[Tool type], Main.[Sub system], Main.Description, Main.Link,
Main.Resolution, Main.Notes, Main.Title
FROM Main
WHERE (((Main.[Tool type])=[Forms]![find]![Tool type]) AND ((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*")) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null)) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null)) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null)) OR (((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null))
ORDER BY Main.[Tool type];


Douglas J. Steele said:
Not easily.

What you could do is have a form with a text box on it and a command button
that runs the query. In the text box's AfterUpdate event, put code to parse
how many different values are in the text box, and modify the query's WHERE
clause from

WHERE MyField LIKE "*" & [Enter word] & "*"

to

WHERE MyField LIKE "*" & word1 & "*"
OR MyField LIKE "*" & word2 & "*"
OR MyField LIKE "*" & word3 & "*"

Post back if you'd like more help with this approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony T said:
I have a repair database that has a memo field. I would like users to find
any or all words from a single input box possibly seperated by commas for
example: motor, electrical, etc.

I can find any single word by using: Like "*" & [Enter word] & "*"
Click to expand...
Click to expand...
 
I wasn't talking about having multiple text boxes: I was talking about
allowing the user to type in dog,cat,cockatiel in the one text box.

Before running the query, you'd need to have code along the lines of:

Dim intLoop As Integer
Dim strWhere As String
Dim varWords As Variant

varWords = Split(Me.InputField, ",")
If IsNull(varWords) = False Then
For intLoop = LBound(varWords) To UBound(varWords)
strWhere = strWhere & "Table.Field Like '*" & _
varWords(intLoop) & "*' AND "
Next intLoop
End If

If Len(strWhere) > 0 Then
strWhere = Left$(strWhere, Len(strWhere) - 5)
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony T said:
What I am hoping for is more like a google search where you can enter
multiple words in 1 box and it will search the contents of the memo for
any
or all words.


I don't want to open multiple boxes because the search is already part of
a
form
that has 2 other fields. The search will return all results of a given
field
if left blank.
see code below:

SELECT Main.[Tool type], Main.[Sub system], Main.Description, Main.Link,
Main.Resolution, Main.Notes, Main.Title
FROM Main
WHERE (((Main.[Tool type])=[Forms]![find]![Tool type]) AND ((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*")) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null)) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null)) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null)) OR (((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null))
ORDER BY Main.[Tool type];


Douglas J. Steele said:
Not easily.

What you could do is have a form with a text box on it and a command
button
that runs the query. In the text box's AfterUpdate event, put code to
parse
how many different values are in the text box, and modify the query's
WHERE
clause from

WHERE MyField LIKE "*" & [Enter word] & "*"

to

WHERE MyField LIKE "*" & word1 & "*"
OR MyField LIKE "*" & word2 & "*"
OR MyField LIKE "*" & word3 & "*"

Post back if you'd like more help with this approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony T said:
I have a repair database that has a memo field. I would like users to
find
any or all words from a single input box possibly seperated by commas
for
example: motor, electrical, etc.

I can find any single word by using: Like "*" & [Enter word] & "*"
Click to expand...
Click to expand...
Click to expand...
 
OK, I think I understand. I'll try it as soon as I get a chance.
Thanks.

Douglas J. Steele said:
I wasn't talking about having multiple text boxes: I was talking about
allowing the user to type in dog,cat,cockatiel in the one text box.

Before running the query, you'd need to have code along the lines of:

Dim intLoop As Integer
Dim strWhere As String
Dim varWords As Variant

varWords = Split(Me.InputField, ",")
If IsNull(varWords) = False Then
For intLoop = LBound(varWords) To UBound(varWords)
strWhere = strWhere & "Table.Field Like '*" & _
varWords(intLoop) & "*' AND "
Next intLoop
End If

If Len(strWhere) > 0 Then
strWhere = Left$(strWhere, Len(strWhere) - 5)
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony T said:
What I am hoping for is more like a google search where you can enter
multiple words in 1 box and it will search the contents of the memo for
any
or all words.


I don't want to open multiple boxes because the search is already part of
a
form
that has 2 other fields. The search will return all results of a given
field
if left blank.
see code below:

SELECT Main.[Tool type], Main.[Sub system], Main.Description, Main.Link,
Main.Resolution, Main.Notes, Main.Title
FROM Main
WHERE (((Main.[Tool type])=[Forms]![find]![Tool type]) AND ((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*")) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null)) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null)) OR (((Main.[Tool
type])=[Forms]![find]![Tool type]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null)) OR (((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((Main.[Sub
system])=[Forms]![find]![Sub system]) AND ((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((Main.Description) Like
"*"+[Forms]![find]![Description]+"*") AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null)) OR (((([Main].[Description]) Like
[Forms]![find]![Description]) Is Null) AND ((([Main].[Sub system]) Like
[Forms]![find]![Sub system]) Is Null) AND ((([Main].[Tool type]) Like
[Forms]![find]![Tool type]) Is Null))
ORDER BY Main.[Tool type];


Douglas J. Steele said:
Not easily.

What you could do is have a form with a text box on it and a command
button
that runs the query. In the text box's AfterUpdate event, put code to
parse
how many different values are in the text box, and modify the query's
WHERE
clause from

WHERE MyField LIKE "*" & [Enter word] & "*"

to

WHERE MyField LIKE "*" & word1 & "*"
OR MyField LIKE "*" & word2 & "*"
OR MyField LIKE "*" & word3 & "*"

Post back if you'd like more help with this approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a repair database that has a memo field. I would like users to
find
any or all words from a single input box possibly seperated by commas
for
example: motor, electrical, etc.

I can find any single word by using: Like "*" & [Enter word] & "*"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top