Two Form Results as Criteria

[jwruble]

I have created reports that get their data from a query. The query gets two requirements (criteria) from a form consisting of two list boxes. So the user selects their desired output from the two pull-down boxes. That part works fine, but I would like for the user to be able to leave the boxes blank, resulting in a much more broad report.

Using "Is Null" statements, I got the process to work when both boxes are left blank. However, if one is blank and the other is specified, the query (or report) returns blank results (error).

Does anybody have any info on this sort of thing.

 

[hennep]

I guess in the query builder you have done something similar to this:

Criteria: | forms.formname.field1 | forms.formname.field2
0r: | is null | is null

try to change it to:

Criteria: | forms.formname.field1 | forms.formname.field2
0r: | is null | is null
| forms.formname.field1 | is null
| is null | forms.formname.field2

 

[jwruble]

Ok, it still doesn't work. In the design view of the query, I have two columns set up (one for each combo box). In the criteria section I have the following:

Criteria: [Forms]![Form Name]![Field1]
Or: [Forms]![Form Name]![Field1] Is Null

When I enter the "OR" line, it automatically creates another column set as:

Field: Expr1: [Forms]![Select Tribe & Status]![Tribal Name]
Criteria: Is Null

This is the only way I heve found to even get it to partially work. As of right now, I can leave both boxes blank and view all results, but I cannot leave ONE of the boxes blank...it always results in a report that says "error".