Keyword search a memo field in an Access database

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.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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


This was first published in June 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top