How to filter and have all results show?

[TooGoon]

This may seem very simple but it is being rather aggravating. Maybe its just being new to the coding side of Access.

I'm using a query's criteria applying a filter through a form. However the IIF(IsNull(forms!form!cboBox), [querycolumn],forms!form!cboBox) code that i use automatically knocks out any blank entry. In the table "Allow Zero Length" has been turned off. What is the value of the field then?
As there are many fields that do not have data entered for them, this is a severe problem.

To trouble shoot this I've tried just selecting all records in a normal query with wildcard criteria, but all of these knock out the blank columns.

 

[RickSpr]

Your problem might be the expression, "forms!form!cboBox". This expression can evaluate to either an object value (the control, cboBox) or the object's default Value property (which is what you want).

Usually, VBA can tell from context which one you mean, by what you're going to assign or compare it to. In an IIf() function in a query's criteria, however, it's tough for VBA to figure out that out, so it may be taking a guess which meaning you intended--and guessing wrong. If that's the case, the expression always evaluates to the control reference, and never to Null, thus messing up your logic.

Try substituting "forms!form!cboBox.Value".

BTW, if that's the problem, it's quite rare, and hardly anybody can figure it out because we're so used to VBA knowing when to use the default property and when the object.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein