Query Giving Unexpected Results

[Toolguy]

Hi All Clever People
I'm Trying to seed a query that displays results from a combo box entry on a form.
I'm trying to filter a query of what's entered in the combo box and show all if the combo box has nothing entered

Code on the query builder window under 'Criteria:'
IIF(IS Null [Forms]![frmItemizedLogBatch]![ComboSearchBatch],Like"*",[Forms]![frmItemizedLogBatch]![ComboSearchBatch])

returns no records if combo box is empty and returns the correct records if combobox has data in it.

That am i overlooking?? I'm getting befuddled

Thanks

 

[LPurvis]

Are you saying you're having problems?
Though not ideal - the concept works, but it will not include any Null valued rows for that field.

To do this in the QBE (query by example - design grid) you need to flip the logic.
Add a calculated column of something like
Expr1: [Forms]![frmItemizedLogBatch]![ComboSearchBatch]

which itself has criteria of
[FieldName] Or Is Null

Such comparison to a literal negates the chance of index optimisation though.
(Ironically the previous method has a better likelihood of such optimisation - but as I say, it doesn't include Nulls in the results).

Building the SQL dynamically depending upon provided criteria gives the best of both.
(But itself is then dynamic and hence no pre-compiled execution plan - but that can be inconsequential compared to an efficient query request).

Cheers.

 

[Toolguy]

Thanks I'll give that a try. I also tried the nz() function but had the same results