Query Problems

[johnj]

I am setting up a query that uses dynamic data based on values in a
form. These form fields are drop downs and are used several places in
the database. I am using the same criteria stings in all of the query
fields but with different form field references. The expression look
like this:

Like
IIf(IsNull([Forms]![QueryForm01]![EncodingLab]),"*",[Forms]![QueryForm01]![EncodingLab])

The goal is to display all records unless the "EncodingLab" field is
populated...and if it is populated, use the value as the query
criteria.

The problem is, it does not display null values for this fields for
some reason. Any Suggestions.

Thanks

 

[Steve Schapel]

Johnj,

The behaviour you are seeing is expected. A criteria of Like "*" will
not return a null because, well, because null is not Like "*".

One way around it is to make a calculated field in the query for all the
fields where a Null may be possible, for example...
FieldForCriteria: Nz([NameOfField],"")
.... and put your criteria in this column.
Another solution is to use this as a criteria instead...
[Forms]![QueryForm01]![EncodingLab] Or
[Forms]![QueryForm01]![EncodingLab] Is Null
(watch for newsreader wordwrap, this is all on one line!)

By the way, the expression you are using could be simplified by the use
of the Nz() function...
Like Nz([Forms]![QueryForm01]![EncodingLab],"*")