Re: query help

[Ian Skinner <iskinner@xxxxxxxxxxx>]

You have the basic idea correct.  You use conditional statements to 
dynamically build your SQL statement.  A couple of hints.

One:
IsDefined() is not going to work, it will always return 'true'.  An 
empty field is still a valid and defined field so it will always pass 
this test.  What you want to test for is no non-white space characters. 
 Try <cfif len(trim(form.aField)) GT 0>  This tests that there is at 
least one non-white space (which the trim function removes) character in 
the field.

Second the easiest way to handle the SQL is to provide a where clause 
that is always false or always true.  Depending on if you want to return 
no records or all the records if other filter values are not provided. 
Then you add to this with the conditional statements.


This will return no records unless proper filter(s) are provided.
WHERE
  1 = 0
  <cfif len(trim(form.aField))>
    OR aField = form.aField
  </cfif>
  <cfif len(trim(form.bField))>
    OR bField = form.bField
  </cfif>


This will return all records unless proper filter(s) are provided.
WHERE
 1=1
  <cfif len(trim(form.aField))>
    AND aField = form.aField
  </cfif>
  <cfif len(trim(form.bField))>
    AND bField = form.bField
  </cfif>