Sue,

One additional possibility if your table is fairly small OR if the field is
not indexed. Add a zero-length string to the field. You can do this even if
the field is indexed, it just means that the index won't be used and the query
will take a bit longer to run.

Field: MySearchField: [Name of Field] & ""
Criteria: Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

You can experiment with this and see if the performance hit is acceptable.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Sue Pari wrote:
Hi,

I'm trying to pass multiple query criteria from form controls. I'm
using the following type of syntax for the criteria:

Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

And this works fine for the fields where there are no Nulls in the data,
but if there are Nulls in this field I don't get those records. Quite
understandable since Like "*" doesn't return Nulls.

What I'm trying to do is to return all records when there is nothing
chosen in the selection box on the form. I've tried a bunch of
different IIfs with various use of quote marks, on the order of:

IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is
Null Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])

but they either don't get all the records or they kick a "too complex"
error.

I'm sure this must be easier than I'm making it. Could anyone give me
some help?

Thanks in advance,

Sue