null values and a query

[salmonella]

I have what I assume is a simple problem- but not for me.

I have a query where, in the criteria box, I put forms![form]![field], where
“form†is a form with unbound combo boxes and “field†is a combo box on the
form from which you can select different things to use as criteria in the
query. This works fine except that as a default I have all the comb boxes
with “*†as a default value so that as a default, all values in all fields
are returned. This creates a problem because if there is a null value in a
field, the “*†will cause the query not to return the record! I assume that
there is a way to use the NZ function so that all null fields are converted
to zero length strings or zero. If this is true, then how is it done or is
there a simpler way around this where I don’t use default “*†in the combo
boxes, etc.

THANKS!

 

[Ofer]

Try this

Where FieldName Like NZ(forms![form]![field], "*")

 

[salmonella]

Thanks,
that, together with zero-length strings, did the job and now with parameter
queries, no records are lost.

thanks again!

Try this

Where FieldName Like NZ(forms![form]![field], "*")
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I have what I assume is a simple problem- but not for me.

I have a query where, in the criteria box, I put forms![form]![field], where
“form†is a form with unbound combo boxes and “field†is a combo box on the
form from which you can select different things to use as criteria in the
query. This works fine except that as a default I have all the comb boxes
with “*†as a default value so that as a default, all values in all fields
are returned. This creates a problem because if there is a null value in a
field, the “*†will cause the query not to return the record! I assume that
there is a way to use the NZ function so that all null fields are converted
to zero length strings or zero. If this is true, then how is it done or is
there a simpler way around this where I don’t use default “*†in the combo
boxes, etc.

THANKS!