Empty unbound combo box won't return null values
Posted on 2009-04-16
I have an unbound form that has many unbound text and combo boxes. After a user has entered/selected the data they want to use to filter the data, they click a button and that runs a query that filters the data as they wanted. This was not a problem before because all the fields they were using before were required and could never be null. I was asked to add another field as a filter option but this field is not required so there are many null values. Now when I run the query, I don't get any lines if that new field has a null. How do I get all lines of data even if that field is null?
I have a query tied to the on click event on the button. That querys criteria is set to the unbound text or combo box on the form. Here is how the criteria is currently written: IIf(IsNull([Forms]![Create_Your_Own_Report_frm]![Combo10]),[Circuit_tbl].[Status],[Forms]![Create_Your_Own_Report_frm]![Combo10]). This works if the user selects a value from this new combo box obviously because it is only looking for that value. The trouble is when this new combo box is left blank. It will not return any lines if this field is null. I tried to return an "" empty string if this is null and that didn't work. Any other suggestions?