Filter and Null

[Andrew]

I have a form with combo boxes. Based on the user's selection. a subform is
queried. (That subform then queries another, but that's not relevant to this
question.) The combo box for requestID has code like this:

If Me.cmb_requestid <> "" Then
Me.Details.Form.Filter = "[requestid] = " & Me.cmb_requestid
Me.Details.Form.FilterOn = True
Else
Me.Details.Form.FilterOn = False
End If

The combo box for the analystID has the same code -- like this:

If Me.cmb_analyst_name <> "" Then
Me.Details.Form.Filter = "[analystID] = " & Me.cmb_analyst_name.Column(0)
Me.Details.Form.FilterOn = True
Else
Me.Details.Form.FilterOn = False
End If

Each combo box works well. But now, ha ha, I want to create a procedure to
AND the filters.

I have this disaster:

If Me.cmb_requestid <> "" Or _
Me.cmb_analyst_name <> "" Then

"[requestid] = " & IIf(Me.cmb_requestid = Null, "[requestid]",
Me.cmb_requestid) & " AND [analystid] = " & IIF(Me.cmb_analyst_name.Column(0)
= Null, "[analystid]", Me.cmb_analyst_name.Column(0)

Me.Details.Form.FilterOn = True
Else
Me.Details.Form.FilterOn = False
End If

The idea being that if one or the other is null I get a WHERE clause like
this:
[requestid] = [requestid] AND [analystid] = [analystid]

but if they both have values, both criteria will appear in the WHERE clause
like this:
[requestid] = 1 AND [analystid] = 20

 

[Allen Browne]

Andrew, download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The example shows how to build a filter string from only the boxes where the
user actually entered something. It demonstrates ANDing various filters, and
handling various data types, including ranges (as well as discreet values)
and partial text matches. It's a technique that's really worth learning.

Also, are you aware that Null is not the same thing as a zero-length string?
Your example tests for "" (a ZLS), but does not test for Null (which would
mean using IsNull().)

 

[Andrew]

Thank you so much. This looks amazing. And, believe it or not, I am aware
that a zero-length string is not the same as a null. I tried so many
iterations with different tests. After awhile I lost my confidence that my
nulls might not in fact be zero-length strings. Thanks again. I can't wait
to dig in.

Andrew, download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The example shows how to build a filter string from only the boxes where the
user actually entered something. It demonstrates ANDing various filters, and
handling various data types, including ranges (as well as discreet values)
and partial text matches. It's a technique that's really worth learning.

Also, are you aware that Null is not the same thing as a zero-length string?
Your example tests for "" (a ZLS), but does not test for Null (which would
mean using IsNull().)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with combo boxes. Based on the user's selection. a subform
is
queried. (That subform then queries another, but that's not relevant to
this
question.) The combo box for requestID has code like this:

If Me.cmb_requestid <> "" Then
Me.Details.Form.Filter = "[requestid] = " & Me.cmb_requestid
Me.Details.Form.FilterOn = True
Else
Me.Details.Form.FilterOn = False
End If

The combo box for the analystID has the same code -- like this:

If Me.cmb_analyst_name <> "" Then
Me.Details.Form.Filter = "[analystID] = " &
Me.cmb_analyst_name.Column(0)
Me.Details.Form.FilterOn = True
Else
Me.Details.Form.FilterOn = False
End If

Each combo box works well. But now, ha ha, I want to create a procedure
to
AND the filters.

I have this disaster:

If Me.cmb_requestid <> "" Or _
Me.cmb_analyst_name <> "" Then

"[requestid] = " & IIf(Me.cmb_requestid = Null, "[requestid]",
Me.cmb_requestid) & " AND [analystid] = " &
IIF(Me.cmb_analyst_name.Column(0)
= Null, "[analystid]", Me.cmb_analyst_name.Column(0)

Me.Details.Form.FilterOn = True
Else
Me.Details.Form.FilterOn = False
End If

The idea being that if one or the other is null I get a WHERE clause like
this:
[requestid] = [requestid] AND [analystid] = [analystid]

but if they both have values, both criteria will appear in the WHERE
clause
like this:
[requestid] = 1 AND [analystid] = 20

 

[Andrew]

And after reading your instructions I see why I wasn't sure if my text boxes
were ZLS or nulls. I never really paid attention to the Zero Length property.