Solved

Creating search KEYWORD query and still not finding the right syntax

Posted on 2004-08-10
6
763 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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