Search boxes

[rosebud289]

I have created a search box to search for keywords in a paragraph contained in a field within a table. Whenever I use SELECT... WHERE variable LIKE '%" &variable& "%', words containing the keywords appear. For example, if I search for the word "day", paragraphs containing words such as "today" and "tuesday" will appear. Being that I'm searching for a whole word within a paragraph, I have to use LIKE in the SELECT statement right? Unfortunately, it's displaying everything under the sun. How can I fix this problem? Thanks!!!

 

[FesterSXS]

try...

"SELECT....WHERE fieldname CONTAINS '" & variable & "'"

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[Sheco]

You could make the LIKE select part of a subquery, and then have the outer query use the equal sign:

SELECT word FROM (SELECT word FROM tblParagraph WHERE word LIKE %day%) WHERE word = 'day'

 

[AnonGod]

put a space in your query - like:

SELECT * FROM tablename WHERE attribute LIKE '% " & variable & " %';

notice the spaces before and after the variable on either side of the quotes.

-Andrew

[TAG]
... If you find my posts helpful, rate me up! ...
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[rosebud289]

The space before and after the variable worked, thanks!!! how do i handle plurals? For example, if someone enters "hat" then the topic that has the word "hats" in it will not be displayed. Thanks again for your help!!!

 

[Sheco]

The problem with adding a space to the search variable is that it will not catch the times when your word is followed or preceded by a character other than a space.

Examples:
* First word of a paragraph may be preceded by a Tab.
* Any word may be followed by a comma, colon, or semicolon.
* Almost any word may be hypenated.
* If the word is in a filename or web address, it may be surrounded with slashes or backslashes.
* Almost any word may be followed by a period, except a presposition... don't end a sentence with a preposition!


Well I pointed out all these problems but I didn't suggest any solution... The thing I posted above has the same problem.

 

[rosebud289]

I think I figured it out. I replaced the second space before the percent sign with an "s". I guess I can do this for all forms of plurals (i.e. es, ies). Please advise, thanks!

SELECT * FROM tablename WHERE attribute LIKE '% " & variable & "s%';

 

[Sheco]

Oh, I didn't even think of plurals!

 

[rosebud289]

The above code works fine if the variable is contained in the middle of a sentence. Because of the space between the percent sign and double quotes, sentences beginning with or ending with the variable are not displayed.

variable LIKE '% " & variable & " %'

How can i fixed this? Thanks!!!

 

[DotNetGnat]

Why dont you include all the options:

variable LIKE '% " & variable & " %' OR variable Like '% " & variable & "s%' OR variable LIKE....

and so on...

-DNG

 

[rosebud289]

I've included all options regarding the plurals, but if i remove the space between the percent sign and double quotes then i will get results for words contained in words. For example, if i search for "opera" then the word "operations" may display. i need to display records for exact words entered into the search box plus the plurals, while making sure all records containing the word at the beginning and end of sentences are displayed. Thanks!

 

[bslintx]

I think if the EXACT number of characters to be matched is known, it is more efficient to use "_" as the wild card

maybe that will help

BSL