asked on

Creating search KEYWORD query and still not finding the right syntax

Hi:

I am trying to create a keyword search in ASP that pulls from an ACCESS database.

I have tried two variations on the same code:

The first pulls all the records from the database (when I know not all are a match)

rsSearchAll.Source = "SELECT * FROM Sites_Orgs WHERE OrganizationName OR HistoricalSignificance LIKE '%keywords%" + Replace(rsSearchAll__MMColParam, "'", "''") & "' ORDER BY MailingListID ASC"

The second attempt pulls NOTHING from the database:
rsSearchAll.Source = "SELECT * FROM Sites_Orgs WHERE OrganizationName LIKE '%keywords%" + Replace(rsSearchAll__MMColParam, "'", "''") + "' OR HistoricalSignificance LIKE '%keywords%" + Replace(rsSearchAll__MMColParam, "'", "''") + "' ORDER BY MailingListID ASC"

First, does anyone know why this phenomenon is occurring. Second, does anyone know how to fix it. What I am trying to do is pull from a form with a textfield labeled (keywords) and match against two columns (OrganizationName and HistoricalSignificance in table Sites_Orgs). Any ideas?

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

htillberg

ASKER

Hey, thanks, I will try these suggestions. I am using Access. Personally I would prefer SQL but I did not build this site that I am working on. I tried with * as a wildcard and got the same results before. My understanding of the Replace(rsSearchAll__MMColParam, "'", "''") is that it is changing ' so that it can be interpreted by the query if that is part of the query. In other words ' is a special key and would not be interpreted unless this is accounted for. I didn't understand what it was doing there either until I was looking at another search engine someone created, where they created a function called EscapeApostrophe to escape the ' character as it has a special delineation.

In short, I think I need it, but if someone else thinks otherwise, please recommend accordingly!

htillberg

ASKER

Wow. I found the answer. Who would have thunk it:

rsSearchAll.Source = "SELECT * FROM Sites_Orgs WHERE OrganizationName LIKE'%"&keywords&Replace(rsSearchAll__MMColParam, "'", "''") &"%' OR HistoricalSignificance LIKE '%"&keywords&Replace(rsSearchAll__MMColParam, "'", "''")&"%' ORDER BY MailingListID ASC"

Even though I'm using Access---it doesn't like * as a wildcard (? why?) but it does need & as a concatenator. I'm giving out the points though, because your suggestion to move the keywords outside of the quotes was immensely helpful. Thanks

GRayL

In Access 2000 you cannot refer to Replace() directly in a query. You can build a function which uses replace() in the code, and then call the function, but you cannot call replace() directly from a query.

htillberg

ASKER

am i incorrect about it working then? When I take the replace out of the query, it returns everything again? I believe you that you can't put Replace directly in a query...but why does it return everything when I take it out of the query?

GRayL

I assume you are running A2000. On your machine try a simple query on a small table:

Select anyfld, replace(anyfld, "t","") from smalltable;

you should get an error message.

Does your query now return the correct number of records? If so, then you are not running A2000.