If Cool, and Joe are not right next to each other in the memo field, or are
right next to each other but not in your order, your query will return no
records.
You could do:
Like "*Cool*" OR LIKE "*Joe*"
But this would involve a lot of work if you wanted to look for records that
contained 15 different names.
What I usually do when I want to search for keywords in a memo field is
construct a table of the keywords, similiar to your second table, with the
difference that I add a
Yes/No field (IncludeThis), that I use to indicate whether I want to include
that word in the search. I'll build this example using two tables
Table1
ID - autonumber
MemoField1 - memo
Table2
SearchFor - text
IncludeThis - Yes/No
Now, write a query that selects all of the Names (I'll use SearchFor as my
field name) from table2 where IncludeThis is True; something like:
SELECT SearchFor FROM table2 WHERE IncludeThis = True
Modify this query in the SQL view as follows:
SELECT SearchFor
FROM (SELECT SearchFor FROM Table2 WHERE IncludeThis = True) as T
This is the start of the process of building a nested subquery. You can now
view this in the query grid, to add the second part of the query. This
second part assumes that table1 contains an ID field that is an autonumber
data type. Since you cannot group by memo fields, you need this ID or some
other unique value that you can use to group by and identify the row. Add
table1 to the query grid, but do not join the two tables, then add the ID
column from table1 to the grid. Insert a computed field that determines
whether the name you are looking for is in the grid. This computed field
will look like:
EXPR:IIF(INSTR([MemoField1], [SearchFor]) > 0, 1, 0)
This expression will return a 1 if the word being searched for is in the
memofield, and a 0 if it is not. If you want to make sure this is working
properly, add the memofield and the SearchFor field to the query grid, but
you don't need to do this. If you added the memofield and the SearchFor
field to the grid remove them, convert the query to a totals query, with a
GroupBy on the ID field, and Sum on the computed field. The query should now
look like:
SELECT tbl_MemoSearch.ID, Sum(IIf(InStr([memoField1],[SearchFor])>0,1,0)) AS
Matches
FROM [SELECT tbl_MemoSearch_For.SearchFor
FROM tbl_MemoSearch_For
WHERE tbl_MemoSearch_For.IncludeThis=True]. AS T, tbl_MemoSearch
GROUP BY tbl_MemoSearch.ID
What this will give you is the ID value of each record in Table1, and the
number of matches between the memofield and the values in Table2 that are
checked. To limit this result set to only those that match all of the
selected values in Table 2, you need to add a Having clause, so that the
resulting query looks like:
SELECT tbl_MemoSearch.ID, Sum(IIf(InStr([memoField1],[SearchFor])>0,1,0)) AS
Matches
FROM [SELECT tbl_MemoSearch_For.SearchFor
FROM tbl_MemoSearch_For
WHERE tbl_MemoSearch_For.IncludeThis=True]. AS T, tbl_MemoSearch
GROUP BY tbl_MemoSearch.ID
HAVING
Sum(IIf(InStr([memoField1],[SearchFor])>0,1,0))=Abs(DSum("IncludeThis","tbl_MemoSearch_For"))
Save this query. Create a new query that includes it and Table1 and link
the two on your ID field. Now all you have to do to look for any number of
names is check/uncheck the IncludeThis column in Table2, then run your query.
HTH
Dale
Then, write your query
