Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Creating search KEYWORD query and still not finding the right syntax

Posted on 2004-08-10
6
Medium Priority
?
780 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 1500 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

618 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