My form ([Loss Ratio Query Form]) has two fields ([FISCAL_YEAR] and [Business]) and I wanted to return all data if one was left blank. I couldn’t ever get it to work in the query. But I ultimately used the query data in a report (the report has two matching fields: [Fiscal Year] and [Business].
For my form, I created a button that you click to generate the report (that's the OpenReport DoCmd). I created the following event procedure and assigned it to the “On Click”. It works like a charm:
DoCmd.OpenReport "Loss Ratio Query", acPreview, "", "IIf(IsNull([Forms]![Loss Ratio Query Form]![FISCAL_YEAR]),[Business]=[Forms]![Loss Ratio Query Form]![Business],[FiscalYear]=[Forms]![Loss Ratio Query Form]![FISCAL_YEAR])and IIf(IsNull([Forms]![Loss Ratio Query Form]![Business]),[FiscalYear]=[Forms]![Loss Ratio Query Form]![FISCAL_YEAR],[Business]=[Forms]![Loss Ratio Query Form]![Business])"
There may be other ways to do it, but this is the only way I found that would work. Good luck.
-T.