Using NZ Function to return ALL records

  • Thread starter Thread starter ghetto_banjo
  • Start date Start date
G

ghetto_banjo

In my query, I am using the NZ function to look at a field on a form,
and if that field is blank I want the query to act like there is no
criteria at all.

Here is my function

Like nz(Forms!frmSearch.txtOrderCategory, "*")


This almost works perfectly. Currently, the query will pull orders
with the specified order category if that field is filled in, or if
the field is blank, it will pull all orders that HAVE an order
category. Some orders do not have a category specified, and these are
not returned. In other words the "*" means all records that have
anything but null. I want to also include the nulls when the field is
blank, how do I accomplish this?
 
hi,

ghetto_banjo said:
In other words the "*" means all records that have
anything but null. I want to also include the nulls when the field is
blank, how do I accomplish this?
Click to expand...
Add this to your criteria:

OR (IsNull(Forms!frmSearch.txtOrderCategory) AND IsNull([yourField]))


mfG
--> stefan <--
 
Still is only grabbing the non-null values. maybe i do not have it
set up properly.


Like nz([Forms]![frmSearch].[txtOrderCategory],"*") Or (IsNull([Forms]!
[frmSearch].[txtOrderCategory]) And IsNull([tblOrders.Category]))



seems like those IsNull statements are just going to return booleans
and not specify it to return null values as well? I dont know....
 
By default, when you open a form and an unbound text box is "blank",
is it a zero-length string or is it Null?
 
Back
Top