I have a user interface that allows a user to interact with a combo box to refine search criteria and the records that are returned (in a subform) from a query. I do not want an empty combo box to mean that the query only returns records with a corresponding empty value. Instead, I want all values returned when the combo box is empty (the user just has not yet interacted with the search menu at that level of detail).
The basic idea I am trying to accomplish is here (put on the criteria line in the query design view):
IIf(IsNull([Forms]![PlanBuilder]![cmbABETOutcomeSearch]),Like "#" Or Is Null,[Forms]![PlanBuilder]![cmbABETOutcomeSearch])
If the combo box has a null value, I want it to return records with any number in that field, including those that have a null value in the field. If the user has specified a value in the combo box, then I want the query to return only records with that value in the field.
The individual components work great. My line
Like "#" Or Is Null
does what I want when entered into the criteria line directly. But I get a syntax error when I embed within iff as I have done above. Any idea how to clear the syntax problem?