Value Passed to Query with nothing selected

[mlocurci]

If I use a query that depends on the value of the dropdown, what if no value is selected in the drop down, what value gets pased? i assummed Null ans wrote my where clause like this:

IIf([Forms]![ReportingMenu]![SupervisorReport]=Null,"Is Not Null",[Forms]![ReportingMenu]![SupervisorReport])

Am I wrong to assume the value is NULL?

 

[rubbernilly]

You are telling it that if the dropdown is null, it should return only the records that read "Is Not Null" in this field. I'm guessing you don't have any of those.

If you mean to return all records if nothing is selected in the dropdown, why not try this in the criteria for that field:

Like [Forms]![ReportingMenu]![SupervisorReport] & "*"

 

[mlocurci]

Correct, non of the records in that field will be NULL. What I am attempting to do is have a form with multiple drop downs. If one drop down is not selected, but others are, I don't want to prevent the query from working, but to assume that there is no criteria for that field. I don't think that will work.

 

[rubbernilly]

Sure it will work, after a fashion.

I've done this a couple of ways. The quickest way is just to change each of the criteria for the query field definitions that are dependent on a dropdown on the form.

Set them each to be "LIKE" the dropdown and an asterisk wildcard character, and they will return all records when none are chosen.

Set the SupervisorReport field criteria:
Like [Forms]![ReportingMenu]![SupervisorReport] & "*"

Set the FlabberPuddle field criteria:
Like [Forms]![ReportingMenu]![FlabberPuddle] & "*"

etc.

Another way to do it is to use the dropdowns in a Function that will return a Filter for the query as a string output, then write the Filter to the querydef before you open it.

There are pros and cons to each, but it can be done.