Results 1 to 3 of 3

Thread: search

  1. #1
    Join Date
    Dec 1969

    Default search

    I&#039;m having some problems making a good search string.<BR>the user can search by 15 criteries, age, height, country......<BR>and is one of the fields like age is empty is means that age is not important and that the search should show the result no mather what age is has. one or more fields can be empty and if all is empty all fields in the db is shown. How can I do this the best way? I&#039;m looked around, but haven&#039;t found the answear I&#039;ve been looking for.<BR>plz help

  2. #2
    Join Date
    Dec 1969

    Default RE: search

    &nbsp;<BR>This is a good challenge and I have something on the lines of what you want But it is huge!<BR>ok <BR>Start off by having the criteria for each passed to a diffent page.<BR>then for example<BR>if len(trim(request("age"))) &#062;0 then<BR> if len(Selec) &#062; 0 then<BR> Selec = Selec & " AND age = " & trim(request("age"))<BR> else<BR> Selec ="age = " & trim(request("age"))<BR> end if<BR>end if<BR>etc for each criteria<BR>then the select statement<BR>reads "select * from &#060;table&#062; Where " & Selec<BR>Gets really big fast<BR>Try it

  3. #3
    Join Date
    Dec 1969

    Default It it were me, Id have a function for

    for passing the name of the criteria and the value of the criteria (and if necessary the name of the db column representing the criteria). The text returned would then be the exact text needed to fit the criteria:<BR><BR>"[country]=&#039;US&#039;" or "[AGE]=50" or "[BirthDate]=#12/12/1912#" or "" if blank<BR><BR>on the other side:<BR> <BR>WhereSQL = ""<BR>loop thru the criteria in some manner<BR> retVal = WhereClause(xxx,xxx,xxx)<BR> if RetVal&#062; "" then<BR> if WhereSQL &#062; "" then<BR> WhereSQL = WhereSQL & " AND (" & retVal & ")" <BR> else<BR> WhereSQL = "Where (" & RetVal & ")"<BR> end if<BR> end if<BR>Wend<BR><BR>SQL = "Select ...... " & WhereSQL<BR><BR>Note that if all the RetVal&#039;s are "" then WhereClause="" and can be placed on sql with impunity.<BR><BR><BR> <BR><BR>
    <BR><BR>Function WhereClause(DBCritName,DBCrit,DBType)<BR>   dim strTemp0,typTemp0<BR><BR>   strTemp0=""<BR>   typTemp0=DBType<BR>   if typTemp0="" then<BR>      typTemp0="STRING"<BR>   end if<BR><BR>   if DBCrit &#062; "" and DBname &#062; "" then<BR>      select case Ucase(typTemp0)<BR>         case "STRING"<BR>            strTemp0=" ([" & DBName & "]=&#039;"<BR>            strTemp0=strTemp0 & DBCrit & "&#039;"<BR>         <BR>         case "NUMERIC"<BR>         case "DATE"<BR>....<BR>      end Select<BR><BR>   WhereClause=strTemp0<BR>End Function<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