Help with nested search of database

Results 1 to 3 of 3

Thread: Help with nested search of database

  1. #1
    Tara L. Guest

    Default Help with nested search of database

    I have a search that will include four drop down menus on a form. People can select one, or leave it blank to select all from each drop down menue. I then need to search the database with the criteria they specified. Which is easy except for if they don&#039t specify critera for one of the choices, it has to return all the records for that field. Any ideas how to do this?<BR><BR>For example:<BR><BR>They can search the database of used equipment by:<BR>Manufacturer: choose one<BR>Year: choose one<BR>model: choose one<BR>Category: choose one<BR><BR>They can choose a specific parameter for any of the above four, or select "all". Then it has to pull the records that meet all of the criteria. I&#039m sure this isn&#039t that hard, I&#039m just drawing a blank.<BR><BR>Thanks!

  2. #2
    Join Date
    Dec 1969

    Default RE: Help with nested search of database

    I don&#039t have time right now to give you the exact coding for this but this is how I do it.<BR><BR>You create a variable called strSQL and give it the value of "Select * From tablename"<BR><BR>You then write some if then statements for each parameter so that if the paramater is not equal to all you change strSQL to<BR>StrSQL = StrSQL & " Where Manufacturer = &#039" & Request("Manufacturer") & "&#039"<BR><BR>You do this for all of the possiblilies and then you end up with a StrSQL that you can use as your SQL statementto do something like DBConn.Execute StrSQL<BR><BR>I hope this helps<BR>

  3. #3
    kumar s Guest

    Default RE: Help with nested search of database

    Hi<BR> This can be done in the following way.<BR><BR>dim manufact,yr,mdl,categ,str<BR>str=select * from table<BR>manufact=request("manufacturer")<BR>yr=re quest("year")<BR>mdl=request("model")<BR>categ=req uest("category")<BR><BR>if manufact&#060;&#062;"All" then<BR>str=str & " " & "where manufacturer=" & "&#039" & manufact & "&#039" <BR>end if<BR><BR>If yr &#060;&#062; "All" then<BR> If manufact="All" then<BR> str=str & " " & "where year=" & "&#039" & yr & "&#039" <BR> else<BR> str=str & " " & "and year=" & "&#039" & yr & "&#039"<BR> end if<BR>end if<BR><BR>if mdl&#060;&#062;"All" then<BR> if manufact="All" and yr="All" then<BR> str=str & " " & "where model=" & "&#039" & mdl & "&#039" <BR> else<BR> str=str & " " & "and model=" & "&#039" & mdl & "&#039"<BR> end if<BR>end if<BR><BR>if categ&#060;&#062;"All" then<BR> if manufact="All" and yr="All" and mdl="All" then<BR> str=str & " " & "where category=" & "&#039" & categ & "&#039" <BR> else<BR> str=str & " " & "and category=" & "&#039" & categ & "&#039"<BR> end if<BR>end if<BR><BR>There are number of ways to form the str SQL string. I hope above one will help u. Try it out.<BR><BR><BR><BR><BR> <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts