Filter in Reports

[Debra Ann]

Microsoft Access 2003:

I have a popup form I use to filter a report. It contains two unbound combo
boxes that the user picks from a list on each. I want the ability for the
user to (1) leave both empty and show all info in a report, (2) choose from
one list and filter only on the one item, or (3) choose from both lists and
filter on both items. I have the following code but my problem is I don't
know how to check if the user left the unbound box empty. I tried If
combobox = "" and If combobox = Null but nothing works. Can you tell me what
I would use to test that the combobox has nothing selected?

Thanks.

Dim strWhere As String

If Me.ApplicableProject = Null And Me.ApplicableStatus = Null Then
DoCmd.OpenReport "Action Items by COLA", acPreview
ElseIf Me.ApplicableProject = Null Then
strWhere = "[Applicable Project] = " & Me.ApplicableProject
DoCmd.Close
DoCmd.OpenReport "Action Items by COLA", acPreview, , strWhere
DoCmd.Maximize
ElseIf Me.ApplicableStatus = Null Then
strWhere = "[Status] = " & Me.ApplicableStatus
DoCmd.Close
DoCmd.OpenReport "Action Items by COLA", acPreview, , strWhere
DoCmd.Maximize
Else
strWhere = "[Applicable Project] = " & Me.ApplicableProject & " And
" & "[Status] = " & Me.ApplicableStatus
DoCmd.Close
DoCmd.OpenReport "Action Items by COLA", acPreview, , strWhere
DoCmd.Maximize

 

[Dennis]

Combobox.ListIndex = -1 when no item has been selected from a combobox

 

[Paolo]

Hi Debra Ann,

Instead of = Null you must use isnull() to check if the combo is void. e.g.
If isnull(Me.ApplicableProject) And isnull(Me.ApplicableStatus) Then

to double check add also
If isnull(Me.ApplicableProject) and Me.ApplicableProject="" And
isnull(Me.ApplicableStatus) and Me.ApplicableStatus="" Then

HTH Paolo

 

[Allen Browne]

Use:
Is Null
instead of:
= Null

The following code is designed to make it really easy to add more than 2
boxes to choose from if you need more criteria options later. Each one tacks
an " AND " on the end, ready for the next one. At the end, you chop off the
trailing 5 characters to get rid of the last " AND ".

Dim strWhere As String
dim lngLen As Long
If Me.ApplicableProject Is Not Null Then
strWhere = strWhere &"([Applicable Project] = " & _
Me.ApplicableProject & ") AND "
End If
If Me.ApplicableStatus Is Not Null Then
strWhere = strWhere & "([Status] = " & Me.ApplicableStatus & _
") AND "
End If
lgnLen = Len(strWhere) - 5 'without trailing " AND "
If lngLen > 0 then
strWhere = Left$(strWhere, lnglen)
end if

The above is right if your 2 fields are both Number types. You need to
include delimiters for Text or Date fields.

Here's an example you can download and play with if it helps:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

And here's some more information about handling nulls:
http://allenbrowne.com/casu-12.html