Returning Null fields with criteria already entered in a query

[TonyB]

Hi,

So I have a query running well, and I use the following Parameters (Criteria) under a field called "AdjNo" in the table called "tblReqLogs":

IIf(IsNull([forms]![frmDispatchReports]![Conbox]),[tblReqLogs].[AdjNo],[forms]![frmDispatchReports]![Conbox])

Picture below to demonstrate if it's not clear:

Attachment 18793



What this does is tell the query to only return only records that match whatever is entered in the "Conbox" text box in the form called "frmDispatchReports". However if "Conbox" is empty, then it will return all records. Essentially, this allows users to specify the parameters of a query, using a form.

This all works fine so long as every record has something entered for "AdjNo". Any records that do not have anything entered for "AdjNo" will not show up at all. Is there any way I can modify my statement so that it will include records that have null values in "AdjNo"?

Hopefully this makes sense!


Thanks

[ranman256]

It should work IF you have tblReqLogs in your query.

[TonyB]

tblReqLogs is in the query (not sure if the picture in the OP works).

[June7]

Attached image not working.

Options:

1. include OR IS NULL in the filter criteria

2. calculate a field in query that converts Null to a another value with Nz() function and use that field in the criteria