Complex Query Without profuse code???

[jpmillr1]

Is there a way to query a table based on up to 6
combo box-based criteria (they can be null or defined by the user) without writing tons of SQL statements?

Thanks!
John

 

[rac2]

Yes. It can be done with one query with 12 conditions. It requires that there be a value in the combo box which is not a valid value in the corresponding column. This value will be used to mean that all values of the column are acceptable. Let's say the value is "All". This could be used also for text fields that users can define.

Here is the method illustrated for three fields.

SELECT * FROM theTable WHERE
    ( colA = [criterionA] OR "All" = [criterionA])
AND ( colB = [criterionB] OR "All" = [criterionB])
AND ( colC = [criterionC] OR "All" = [criterionC])

You could do this with a null value I suppose. And you could use any old value, doesn't have to be "All" For numeric columns you would use something like -1 or 0 instead of "All".