Problem solve Get help with specific problems with your technologies, process and projects.

Keyword search a memo field in an Access database

I'm attempting to write an SQL statement that will perform a keyword search on a memo field in an Access database. I require exact matches only. I am using VBscript. If I use % keyword % with a space at the beginning and end of each word, it will return exact matches but will not return a record when the word is preceeded or followed by a carriage return in the memo field. I found your suggestion of using ALT-0013 to find the carriage return and it works, IF I type in the exact word. But now if I type in a portion of a word only, it still returns the record. It seems that it recognizes the ALT-0013 when a carriage return is present, but ignores it when there isn't one. Can you help with this question? Thank you.

What you need to do is use multiple tests in the WHERE clause. Here's how I would search for the exact match --

 where memofield like '% keyword %'
     or memofield like 'keyword %'
     or memofield like '% keyword'
     or memofield like '%'+chr(13)+'keyword %'
     or memofield like '% keyword'+chr(13)+'%'

These five tests are to find the keyword

  • in mid-sentence
  • at the start of the entire field
  • at the end of the entire field
  • at the start of a line after a line break
  • at the end of a line before a line break

So you can see, an exact match is trickier than just padding it with blanks.

The bad news is, it's even worse than the above. You will have to search for quite a few additional combinations, to allow for punctuation. For example, consider the keyword "fun" in this sentence --

Fun? That might be fun, but not too much fun; too much "fun" is no fun.

Notice that the keyword can be followed immediately by a question mark, comma, semi-colon, quote, or period. The good news is that you can search for those other special characters following the keyword like this --

 or memofield like '% fun[?,.;"]%'

This detects punctuation immediately after the keyword. You will still want to test for a quote in front of the keyword --

 or memofield like '%["]fun %'

But notice that each of the above conditions (punctuation after and before the keyword) was coded with a blank on the opposite side. So to be thorough, you would have to code up multiple conditions in combination with the other possibilities -- start of the field, end of the field, start of a line, and end of a line. All in all, I think you are looking at maybe 15 or more combinations.

Are you sure you need exact matches? I would just ignore the blanks, line breaks, and punctuation, and do a simple partial match --

where memofield like '%keyword%'

Sure, you'll get "functional" and "funny" along with "fun" but it's a lot easier to code and might be faster in execution, too.


For More Information


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.