Searching memo fields for words containing a hyphen returns only one result

I have built a search feature for a site which searches 4 fields, 2 in one table and 2 in another. It's an Access database. It works a treat when searching for anything except something like "I'd", or "it's" etc. Anything with a hyphen in returns one result, or less than there in fact are.

My SQL statement is as follows:

strSQL = "select id as strID, tableID as strTableID, tagline as strTagline, content AS strContact FROM news where tagline LIKE '%" & searchString & "%' OR content LIKE '% " & searchString & "%' UNION ALL select id as strID, tableID as strTableID, tagline AS strTagline, content AS strContent from databank where tagline LIKE '%" & searchString & "%' OR content LIKE '%" & searchString & "%' ORDER BY strID"

Hope you can help!

did you try using this where you have searchString

since most ms db stuff doesn't like the ' character. let me know if this works

Yep, changed all instances of ' in the database to '', then searched for '' instead. Again, returns the same incorrect number of results, identical to that before

just an idea. you shouldn't have to change your text in the database to '' (two single quotes) you should just use the when you are inserting it, and use that as well when you are searhing for it. also do any of the fields you are searching contain NULL values?

Sorry, I realise I should use



when inserting the records in the first place. I just meant that for the purpose of testing this I manually changed all entries to reflect this rather than re-adding new database items via the web site front end.

None of the records have NULL values either. Thanks for your continued help with this matter.

I have switched to trying a paramaterized statement now, in an effort to get it to work and improve performance, using a query in access itself called 'qrySearch' which I call from my ASP. However, the query doesn't seem to return any results at all, regardless of what I search for. It is as follows:

PARAMETERS searchText Text ( 255 );
SELECT id as strID, tableID AS strTableID, tagline AS strTagline, content AS strContact FROM news WHERE

(((tagline)LIKE[searchText]))


OR

(((content)LIKE[searchText]))


UNION ALL select id AS strID, tableID AS strTableID, tagline AS strTagline, content AS strContent FROM databank WHERE

(((tagline)LIKE[searchText]))


OR

(((content)LIKE[searchText]))
ORDER BY strID;

I've tried changing my query in access to the following as you suggested, but still no joy. It returns nothing regardless of what I enter as the search parameter. Even a simple search for "a" returns no matches, and this is wholy in access itself, not even using ASP. I'm starting to loose my mind here. Am I being really thick?

PARAMETERS searchText Text ( 255 );
select id, tagline, content FROM news WHERE

(((tagline)LIKE[searchText]))


OR

(((content)LIKE[searchText]))

UNION ALL select id, tagline, content FROM databank WHERE

(((tagline)LIKE[searchText]))


OR

(((content)LIKE[searchText]))
ORDER BY id;