Solved

Creating search KEYWORD query and still not finding the right syntax

Posted on 2004-08-10
6
769 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:htillberg
  • 3
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 11763620
just a quick question,

OrganizationName LIKE '%keywords%" + Replace(rsSearchAll__MMColParam, "'", "''")

what is %keywords%, because its inside the string, its gonna search for something like that

and why have u added in Replace

I would of thought it would be something like

rsSearchAll.Source = "SELECT * FROM Sites_Orgs WHERE OrganizationName LIKE '%" + keywords + "%' OR HistoricalSignificance LIKE '%" keywords "%' ORDER BY MailingListID ASC"


however u access variables in ASP, correct above as appropriate (i.e, use of keywords)

also is this access or sql server db you are using

access tends to use * as wildcard
sql server is %

0
 

Author Comment

by:htillberg
ID: 11763778
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!
0
 

Author Comment

by:htillberg
ID: 11763963
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:GRayL
ID: 11763986
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.
0
 

Author Comment

by:htillberg
ID: 11764248
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?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11764702
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.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question