How can query display info based on cascading combo boxes when box "isnull"

[shocktea]

I have a form with cascading combo boxes pulling from a table. They work perfectly, no worries. My problem now is if I do not enter information in every combo box (i.e. only two out of four combo boxes), how can I still run the query and get the appropriate information?

For example (these are my combo box titles in order):
Product
Type
Customer
Contract #

I don't want to necessarily look by Contract # all the time, but sometimes just by the general Product and Type to get a larger view. How do I set up expressions/criteria in my query to accurately produce that information? Right now it just produces a blank query table if I don't fill out all the boxes.

I've tried a couple of expressions with "isnull" criteria, but I must be doing it incorrectly. Please help! Thanks!

 

[James Dudden]

Re: How can query display info based on cascading combo boxes when box "isnull"

You could do it something like this:

First remove all filter criteria in the query.

Then create a new column and put this in the field:
IIf([Forms]![FormName]![ControlName]="" Or IsNull([Forms]![FormName]![ControlName]),[FieldName] Like "*" Or IsNull([FieldName]),[FieldName]=[Forms]![FormName]![ControlName])

Then in the criteria put:
<> False

NB: You need to replace 'FormName' with the actual name of your form and 'ControlName' with the actual name of the control and 'Fieldname' with the actual name of your field.

 

[shocktea]

Re: How can query display info based on cascading combo boxes when box "isnull"

I ended up doing:

Field:
[Category]=[forms]![frmIssue]![comboCategory] Or [forms]![frmIssue]![comboCategory] Is Null

Criteria:
True


I didn't try your way, but that could work too. Thank you for taking the time to post!