Filter report using query

[Guest]

My report has department and division level. Under one department, there
could be many divisions.
I have set up a form with combo boxes. Once the user selects the department
and division he/she wants to print, he/she can click on a command button to
open the report.
A filter query was set up in the background to filter the records.
Here comes my problem. It works fine if I select both department and
division. But I would like to print out the whole department if I leave the
division combo box blank. The filter doesn't work. It shows errors in the
report.
The query is like this:

Under department criteria:
[Forms]![frm_DataEntry]![ComboSelectDept]

Under division criteria:
IIf([Forms]![frm_DataEntry]![ComboSelectDiv] Is
Null,"*",[Forms]![frm_DataEntry]![ComboSelectDiv])

It seems that "*" doesn't work in this case. Can anybody help me? Thanks a
lot.

 

[fredg]

My report has department and division level. Under one department, there
could be many divisions.
I have set up a form with combo boxes. Once the user selects the department
and division he/she wants to print, he/she can click on a command button to
open the report.
A filter query was set up in the background to filter the records.
Here comes my problem. It works fine if I select both department and
division. But I would like to print out the whole department if I leave the
division combo box blank. The filter doesn't work. It shows errors in the
report.
The query is like this:

Under department criteria:
[Forms]![frm_DataEntry]![ComboSelectDept]

Under division criteria:
IIf([Forms]![frm_DataEntry]![ComboSelectDiv] Is
Null,"*",[Forms]![frm_DataEntry]![ComboSelectDiv])

It seems that "*" doesn't work in this case. Can anybody help me? Thanks a
lot.

When you use a wildcard character you must use the Like operator.

Like IIf([Forms]![frm_DataEntry]![ComboSelectDiv] Is
Null,"*",[Forms]![frm_DataEntry]![ComboSelectDiv])

 

[Guest]

Another option will be,

Leave this
Under department criteria:
[Forms]![frm_DataEntry]![ComboSelectDept]

And then create anther field in the Query
[Forms]![frm_DataEntry]![ComboSelectDept]
And in the criteria section write (in the second line of the criteria - so
it will be OR)
Is Null

In SQL:

Select * From TableName Where department =
[Forms]![frm_DataEntry]![ComboSelectDept] Or
[Forms]![frm_DataEntry]![ComboSelectDept] Is Null

 

[Guest]

Thank you, Ofer. I have just tried what you suggested. But it doesn't work.

I think in this case, it shouldn't be a "or" statement, but "iif".

Another option will be,

Leave this
Under department criteria:
[Forms]![frm_DataEntry]![ComboSelectDept]

And then create anther field in the Query
[Forms]![frm_DataEntry]![ComboSelectDept]
And in the criteria section write (in the second line of the criteria - so
it will be OR)
Is Null

In SQL:

Select * From TableName Where department =
[Forms]![frm_DataEntry]![ComboSelectDept] Or
[Forms]![frm_DataEntry]![ComboSelectDept] Is Null


--
Good Luck
BS"D

My report has department and division level. Under one department, there
could be many divisions.
I have set up a form with combo boxes. Once the user selects the department
and division he/she wants to print, he/she can click on a command button to
open the report.
A filter query was set up in the background to filter the records.
Here comes my problem. It works fine if I select both department and
division. But I would like to print out the whole department if I leave the
division combo box blank. The filter doesn't work. It shows errors in the
report.
The query is like this:

Under department criteria:
[Forms]![frm_DataEntry]![ComboSelectDept]

Under division criteria:
IIf([Forms]![frm_DataEntry]![ComboSelectDiv] Is
Null,"*",[Forms]![frm_DataEntry]![ComboSelectDiv])

It seems that "*" doesn't work in this case. Can anybody help me? Thanks a
lot.

 

[Guest]

Thank you, guys. I have figured it out myself. The answer is actually quite
simple.
Under division division criteria, write:
like "*" & [Forms]![frm_DataEntry]![ComboSelectDiv]