Querying memo field

[Pete Provencher]

Using Access 2000:

Is it possible to query on a word in a memo field. I have a memo field that
lists all of the type of law that a particular attorney has expertise. I
would like to query the memo field for all the attorneys that have the word
criminal. I tried using "like" but that doesn't work. It seems like I have
seen this done before.

Pete Provencher

 

[Douglas J. Steele]

Did you also include wildcard characters when you used Like?

SELECT .... FROM MyTable WHERE Field1 LIKE '*criminal*'

Depending on how you're doing your query, you may need to use % instead of *
as the wildcard symbol:

SELECT .... FROM MyTable WHERE Field1 LIKE '%criminal%'

 

[Pete Provencher]

Your first example worked perfectly. Another question I now have is it
possible to just have the words that I serched on to be displayed. This
might be pushing the envelope a little. Some of the lawyers might have up to
30 different types of expertise and to have them all displayed takes up
quite a lot of room.

Pete Provencher

 

[Douglas J. Steele]

You'd be much better off redesigning your database to store the information
more appropriately.

You should have a table of expertises (presumably with a primary key of
something like Expertise_Id), a table of attorneys (again, presumably with a
primary key of something like Attorney_Id), and an interesection table
representing the many-to-many relationship between the two tables (the
primary key of this intersection table would be both Attorney_Id and
Expertise_Id). Then, it's trivial to do what you're trying to do.