hi, Anyone know how to solve, I have a program as follows: However, it only can search one by one. However, if I want to do multiple criteria search as according program.


YOu can see the result page on the url:
http://internal.quickentextiles.com.hk/sampleroom/byweight.asp?searchtype='Alph'
Once I input "15" on the textbox under by weight and two more criteria such as I input "streaky" under item category and "blue-yellow" under color column. Now all together 3 criteria. The result will only display
as follows with very precise search in 3 inputted criteria, it can be type in.

weight product code item category color twill
15 S957 Streaky Blue-Yellow 4L


However, the following code only can do one by one search. For instance,
I input "15" on the by weight, it sort all of the "15" of the weight of the entire database. It can't be multiple criteria to search. How can I work it out with following source code of SQL.


Dim DbConn
Dim searchtradenameAZ
Dim gettype
Dim getparam
Dim inparam
Dim searchtradenameAZSQL
Dim sURL

Application.LOCK
'Create connection
Set DbConn = Server.CreateObject ("ADODB.Connection")
DbConn.Connectiontimeout=3
DbConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("sroomsearch.mdb") & ";user=nil;password=nil"
Set searchtradenameAZ = Server.CreateObject ("ADODB.Recordset")

gettype = trim(request("searchtype"))
getparam = trim(request("searchAlpha"))

' replace quotes
IF ( gettype = "'Alph'") THEN
inparam = replace(getparam,"'","") & "%"
ELSE
inparam = "%" & replace(getparam,"'","") & "%"
END IF

searchtradenameAZSQL = "select products.name as ProductName, suppliers.name as SupplierName, weight.name as WeightName, color.name as ColorName, " & _
" tradename.name as TradeName, suppliers.ID as SupplierID " & _
" from products, tradename, suppliers, weight, color, pointers where " & _
" weight.name like '"& inparam &"' and " & _
" pointers.Productid = products.id and " & _
" pointers.supplierid = suppliers.id and " & _
" pointers.weightid = weight.id and " & _
" pointers.colorid = color.id and " & _
" pointers.tradenameid = tradename.id order by weight.name"

Set searchtradenameAZ = Server.CreateObject("ADODB.Recordset")
searchtradenameAZ.Open searchtradenameAZSQL, DbConn, adOpenStatic
%>


thanks
gar

The main problem you have is that you have separate FORM tags for each search type. What you need to do is use a single FORM tag pointing to a single result page. Then you can test each field and determine if it should be included in the SQL statement.