Form-based query to return matched value or all records including NULLs

[baulrich]

I have a query that is based on a combo-box on a form. (Actually it's on a text box, which has a value based on the combo-box selection.

The text box expression is: =IIf(IsNull([combo_job_category].[Column](0)),"*",[combo_job_category].[Column](0))

- which says in English: If the user chooses an item from the list, use it as the query criteria.... otherwise use "*" as the query criteria.

Then, in the query itself.... The field in question (job_category) is filtered with this criteria. Note that job_category

Like [Forms]![frm_job_Filter]![search_job_category]

The problem with this is, it only works if the user has selected a combo-box item. If they haven't, they query doesn't return records where job_category Is Null.

So I tried"

Like [Forms]![frm_job_Filter]![search_job_category] or Is Null

The problem with this is, it does include the records where job category Is Null.... even if the user has selected a combo-box item.

What can I do, with access query builder, to search for the item if a combobox item is selected, but if not.... show all records, including nulls.



thanks in advance...

[June7]

Try wildcard in the query parameter:

Like [Forms]![frm_job_Filter]![search_job_category] & "*" Or Is Null

If that doesn't work try using a query that forces a value for all records, even the nulls. For text fields:

Nz([fieldname], "")

or

Nz([fieldname], "none")

Then just use the wildcard in the query parameter under that constructed field.