Access on the Web - ASP - Searching

[ladyck3]

I hope this is the right forum, if not, please direct me to where I need to be.

I just uploaded a database to the internet and I have an asp page for users to search.... but the search that is used on this ASP page allows the user to only search one word. This ASP page was borrowed (with permission) and I simply changed the fields 'n things to make it work... I don't know ASP or the coding... I have knowledge enough to see what is there and to manipulate it somewhat.

The author of this original resource is not knowledgeable enough to help me to expand this search feature so I need help PLEASE. To read a book is frivolous, it just won't sink in, I need help, like Beginner 99, not as high as 101.

The page/code can be found at

http://ladyck.com/data/workbasket.asp

What do I need? How do I make this search field have the capability of searching for more than one word at a time. This is an index of out of print magazines on crafts. A user can enter the word "rug" and find all of the entries that have RUG in them.... but what if they want to look for "knit rug" they can't... Knit will pull up bazillions of things and "rug" much fewer but there's no way to pull up just "knit rugs"

Please help me to understand what I need to do to beef up this search mechanism. I'll beg if I have to

Ladyck3

LadyCK3
aka: Laurie

 

[dhookom]

This is basically an ASP question since the code you will need is vbscript or other, not Access. I expect your code would need to parse out all the individual words and build a where clause for your sql string like:
FieldA + FieldB + FieldC Like '%Word1%' or
FieldA + FieldB + FieldC Like '%Word2%' or
FieldA + FieldB + FieldC Like '%Word3%' or
FieldA + FieldB + FieldC Like '%Wordx%'

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fneily]

Or have a combobox of categories and then another with the items within that category. So the first combobox will filter the second and then use both selection in your query.

 

[Remou]

In ASP, code might look something like:

strKeywords = Request.QueryString("Keywords")
astrKeyArray = Split(strKeywords)
For Each kword in astrKeyArray
	strSearchSQL = strSearchSQL _
                & "And Field1 Like '%" _
                & Server.HTMLEncode(kword) & "%'"
Next
strSearchSQL = Mid(strSearchSQL,5)

 

[JoeAtWork]

It partly depends on whether you want to search the two words as a phrase, "knit rug", or seperately, "knit" and/or "rug". As you'll see in many search engines, they often have radio buttons to let the user choose if they want to "search by phrase", "match all words", or "match any words".

You would then build the SQL statement according to what the user choose. So the WHERE clause could be any of the following:

WHERE Field1 LIKE '%knit rug%'

WHERE Field1 LIKE '%knit%' AND Field1 LIKE '%rug%'

WHERE Field1 LIKE '%rug%' OR Field1 LIKE '%rug%'

 

[JoeAtWork]

uuuggg!

The last line should be:

WHERE Field1 LIKE '%knit%' OR Field1 LIKE '%rug%'

 

[ladyck3]

I want to apologize for my lack of response today. I am SO FRUSTRATED, this site is not blocked from work. I use IE7 on Vista here at home, and was running IE6 on XPSP2 until yesterday at work when I updated to IE7. I am unable to hit this site now. Other's can even with IE7 but I cant. I even backed down to Ie6 again and still cant. I know it has to be some stupid setting somewhere but I have no idea where and thus have not had a chance to digest the information provided.

I did have someone capture it and email it to me and shared it with my partner in crime at work and it helped spark his train of thought so we're working on it and I thank you.

I think he found a workaround with your gentle push(es)... and really do thank you and again apologize for what may have seemed like my lack of interest. I can assure you that is not the case.

LadyCK3
aka: Laurie

 

[JoeAtWork]

The site has been up and down lately, one minute it is working fine the next not. It's become second habit to me when posting on Tek-Tips to always copy my text before hitting the Submit button, otherwise I may be sending my carefully crafted words to oblivion.

 

[PaulBricker]

This is right out of an asp page that I use to find work orders from our intranet system. The user can enter a number or a name and it just uses a conditional statement to determine which value was entered.

<%
Dim myInput
myInput = request.form("myID")

If IsNumeric(myInput) Then
strQuery ="Select * FROM qryWorkOrders Where qryWorkOrders.[WONumber] = " & myInput
Else
strQuery="Select * FROM qryWorkOrders Where qryWorkOrders.[Person_Requesting] Like '%" & myInput & "%'"
End If
Dim objConn, dbPath
dbPath = "C:\WEBSITES\physplantdb\PysPlant2002.mdb"
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
objConn.Open strProvider %>


Paul