Using NZ Function to return ALL records

[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?

 

[Stefan Hoffmann]

hi,

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?

Add this to your criteria:

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


mfG
--> stefan <--

 

[ghetto_banjo]

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....

 

[Jeff Boyce]

"blank" is an ambiguous term.

"blank" is what we see. Access sees nothing at all (Null), or a space, or a
zero-length string.

Are you testing for all possible conditions?


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[ghetto_banjo]

By default, when you open a form and an unbound text box is "blank",
is it a zero-length string or is it Null?