multi column filtering

[Guest]

Hi everybody,

Suppose i have a combo box for every column on a form (say 5-6 combo boxes)
which filter the records acording to combo box values. I tried to methods 1.
use comboobox values as filters and use "form.filter" property and 2. use
these values in SQL statement as where clause and assign it to
"form.recordsource" property.

Actually these 2 methods both work fine, but the problem arrise when a combo
box is empty, because I use "AND" statement between every combo box value.
What is the best way to ignore empty comboboxes ?

Thank you.

 

[Marshall Barton]

Suppose i have a combo box for every column on a form (say 5-6 combo boxes)
which filter the records acording to combo box values. I tried to methods 1.
use comboobox values as filters and use "form.filter" property and 2. use
these values in SQL statement as where clause and assign it to
"form.recordsource" property.

Actually these 2 methods both work fine, but the problem arrise when a combo
box is empty, because I use "AND" statement between every combo box value.
What is the best way to ignore empty comboboxes ?

Check to see if each combo box has a Null value before
concatenating it into the where clause string. For example:

If Not IsNull(Me.combo1) Then ' field1 is numeric
strWhere = strWhere & " And field1 = " & combo1
End If
If Not IsNull(Me.combo2) Then ' field2 is text
strWhere = strWhere & " And field2 = """ & combo1 & """"
End If
If Not IsNull(Me.combo3) Then ' field3 is date
strWhere = strWhere & " And field3 = #" & combo3 & "#"
End If
strWhere = Mid(strWhere, 6)

Then use strWhere in the form's record source SQL
statement's WHERE clause. You could instead use it as the
form's Filter property, but, for multiple reasons, I really
don't like using the Filter property.

 

[John Nurick]

Hi Adam,

IMHO the best approach is to write the code that builds the SQL
statement so it only includes combo boxes that have values. Something
like this air code:

Dim strSQL As String

strSQL = "blah blah WHERE "

If Not IsNull(cbo1.Value) Then
strSQL = strSQL & "Field1=" & cbo1.Value & " AND "
End If
If Not IsNull(cbo2.Value) Then
strSQL = strSQL & "Field2=" & cbo2.Value & " AND "
End If
....
'cut off superfluous terminal AND
strSQL = Left(strSQL, Len(strSQL - 5))
strSQL = strSQL & ";"