Using search that will recognize certain things

[timely]

I have a search function created that will allow people to look for titles but two issues I am coming accross is that if you place a ' in the query it sees it as end of the quary item for that field and will error out. The other is if you put in an extra space at the end it will not locate the search. I know I could use two strings and take care of it but that would be several lines of coding and wanted to see if anyone knew of a quicker way to check for these and make is so that the ' is recognized as part of the title and that the extra space at the end is not part of the search. Also the ability to check that no other punctuation will cause it to error out.

Here is my current code for that section:

if request("type") = "1" then
rsArticle.Open ("SELECT * FROM article WHERE Title LIKE '%" & Replace( Trim( sSearch ), " ", "%' AND Title LIKE '%") & "%'" & " OR Summary LIKE '%" & Replace( Trim( sSearch ), " ", "%' AND Summary LIKE '%") & "%' OR Author LIKE '%" & Replace( Trim( sSearch ), " ", "%' AND Author LIKE '%") & "' ORDER BY IssueNo DESC"), cnArticle,1,1
response.write "<p align='left'><font face='verdana, arial' size='1'> &nbsp;Search result(s) for&nbsp; &quot;<b>" & sSearch & "</b>&quot; " & rsArticle.RecordCount & " articles total</font></p>

 

[Genimuse]

To eliminate the single quote issue you need to replace single single-quotes with double single-quotes, like:

Replace(YourTerm, "'", "''")

To trim the white space off the beginning and end of your search term you can use the Trim command:

Trim(YourTerm)

 

[timely]

Okay I got the trip to work properly, spaces where in when they did not need to be. Okay as for the other issue I must not be understanding what you mean. Lets say I put in houser's in the search here is what the error says:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'Title LIKE '%houser's%' OR Summary LIKE '%houser's%' OR Author LIKE '%houser's' ORDER BY IssueNo DESC'.

 

[Genimuse]

When you put your SQL string together -- and I know this sounds weird -- you need to replace all instances of a single-quote (the apostrophe character) with two of them in a row. SQL translates two apostrophes in a row as a single actual apostrophe rather than as a single-quote surrounding a string literal. So the SQL will look like:

'Title LIKE '%houser''s%' OR Summary LIKE '%houser''s%' OR Author LIKE '%houser''s' ORDER BY IssueNo DESC'

You use

Replace(YourSearchTerm, "'", "''")

to replace a single one with two of them.

Looks weird, but it's what SQL likes. Make sense?