Solved

Creating search KEYWORD query and still not finding the right syntax

Posted on 2004-08-10
6
764 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now