searching by keyword

Hi I havea form which interrogates this page."key" is a text box into which the user can write,"city and cat" are dropdown menus.The problem is that this code doesnt search for my keyword inserted by the user into "key". It just puts the whole content of the database onto the page.How do I change (title like '%"&key&"%' OR text like '%"&key&"%') so that it only gives back records containing the inserted keyword in the fields text or title? Thanks to anyone who can help

<% key=request.form("key")
city=request.form("city")
cat=request.form("cat")%
<%if cat ="none" then%>please select a category<%ELSE%>
<%sql="select * from users where title like '%"&key&"%' OR text like '%"&key&"%' AND city like '%"&city&"%' AND cat like '%"&cat&"%'"%>

Hi!

I have a similar problem with a text box where all records are returned when the query on the data the user enters in the box is executed. I have attached my code below. Maybe if you have a chance you can look at mine an give me some pointers on how you use the syntax for limiting the search of the db to just the one record associated with that ID# (that's my field I need to search). Here's my code. it works well to merge the data into a Word document, except for the single merge problem.

Private Sub Text151_BeforeUpdate(Cancel As Integer)

Dim objWord As Word.Document
Set objWord = GetObject("C:\aden.doc", "Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource _
Name:="C:\DoctorDB.mdb", _
LinkToSource:=True, _
Connection:="QUERY QryAdenCard", _
SQLStatement:="SELECT * FROM [QryAdenCard]"
strSQL = " SELECT QryAdenCard.ID#, FROM QryAdenCard WHERE (((QryAdenCard.ID#)= '" & strID# & "));"
objWord.MailMerge.Execute
objWord.Application.Documents(2).Close wdDoNotSaveChanges
DoCmd.Hourglass False
Set objWord = Nothing
Set objDoc = Nothing
End Sub

Not that sure about Asp but..

sql="select * from users where title like '%"&key&"%' OR text like '%"&key&"%' AND city like '%"&city&"%' AND cat like '%"&cat&"%'"

IS not the best way to write SQL

Assuming that the % is a wildcard charcter used with ASp (like SQL Server) that means "can be anything" (Access uses *).

You should use a statement like
sql="select * from users where title like '"&key&"' OR text like '"&key&"' AND city like '"&city&"' AND cat like '"&cat&"'"

If you really want to use a wildcard include that as part of the variable when needed. By including them before and after(by default) you are telling the DB that I want every record where my keyword exists as part of that field.

For eaxmple if you have entered a A as criteria for the Title, you old SQl would bring back every record that had an "a" somewhere in the field. And since City and Cat as drop downs, the only time you wish to use a Wildcard is when they don't have one selected. If they selected on you don't want to use a wildcard.

Good Luck

S-

Thanks for your help.this stopped all the records being showed only now none of the records are shown even if the keyword exists.

Are you sure you are passing you wildcard character through in the variable

You enter a value in to key, you should also enter a * (or %) into both City and Cat.

i do this all the time and don't have any problems.

Good Luck

S-

the entire form page and form processing page look like this:

FORM.HTML
<form method="post" action="adsearchresult.asp" name="">
<input type="text" name="key" size="20">
<select name="city">
<option value="london">london</option>
<option value="cambridge">cambridge</option>
</select>
<input type="submit" name="submit" value="search">
<input type="reset" name="submit2" value="Reset">
</form>
---------------------------------------------
adsearchresult.asp

<%key=Request.querystring("key")%>
<%city=request.form("city")%>
<%sql="select * from users where title like '"&key&"' OR text like '"&key&"' AND city like '%"&city&"%'"%>
<%Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "driver={Microsoft Access Driver (*.mdb)};dbq=e:\users\ads.mdb"
Set RS=OBJdbConnection.Execute(sql)%>
<%Do while NOT RS.EOF%>
<%rec=rec+1%>
<%response.write(RS("title"))
title=rs("title")%>
<%

RS.MoveNext
Loop
RS.Close
OBJdbConnection.Close%>

Like I mentioned earlier i am not sure about the ASP....

So i don't know if I will be of any additional help to you. Post you question on the ASP part of the forum. Most everyone int eh Access part of the forum program in Access or VB only. Most ASP related questions go unanswered.

S-