Search for MULTIPLE words within ONE field??

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   Hey guys,
I've created my first search page using ASP. Whilst the search function works and data can be obtained, the results will only be successful if the word i try to search is the first word in any entry in one field.
For example...

if i search for BALL
i will get nothing...
however if i search for GLOW BALL i will get a result
i will receive a result...
similarly if i search for GLOW i will get multiple results?
How can i structure the query so that i can search for any of the words within the field and be guaranteed to get a result?
the link for the search form is
http://www.galaxyworldimports.com.au/search.html

Thanks for any help!!

a sample of the asp code is listed below:

]<%
 'Open up a connection to our Access Database
 'that stores the customer information.
 Set MyConn = Server.CreateObject("ADODB.Connection")
      MdbFilePath = Server.MapPath("Databases/gal_imports.mdb")
      MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"

 'Read in the charactes the user entered
 Dim strProductNameQ
 strProductNameQ = Trim(Request("ProductNameQ"))

 'Create our SQL statenebt variable
 Dim strSQL
 strSQL = "SELECT ProductName, ProductPrice FROM Products WHERE ProductName LIKE '" & _ 
  strProductNameQ & "%' ORDER BY ProductName"

 'Create a recordset object instance, and execute the SQL Statement
 Dim objRS
 Set objRS = Server.CreateObject("ADODB.Recordset")
 objRS.Open strSQL, MyConn

 'Determine whether or not there are any records in objRS
 If objRS.EOF then
  'No records were returned for the characters entered by the user
  Response.Write "There are no products that contain " & _ 
   UCase(strProductNameQ)

 Else
  'There are one or more products that meet the condition entered by the
  'user. Display these products...

  Response.Write "<B>A Listing of Products beginning with " & _
   UCase(strProductNameQ) & ":</B><BR>"

  Do While Not objRS.EOF
   Response.Write objRS("ProductName") & " - " & _ 
     FormatCurrency(objRS("ProductPrice")) & "<BR>"

   'Move on to the next customer
   objRS.MoveNext
  Loop
 End If

 'Clean up our ADO objects
 objRS.Close
 Set objRS = Nothing

 MyConn.Close
 Set MyConn = Nothing

 %>