Return ALL records with NZ function

[ghetto_banjo]

I have a query where I use the NZ function in the criteria to
reference a form field. If that form field is null, i want the query
to return ALL records. This almost works. Currently, if the form
field is null, it returns all records where the table field is NOT
null. I need those records containing the Null value to also be
returned though. Here is my function:

Like nz([Forms]![frmOrder].[txtOrderCategory],"*")


any suggestions?

 

[Dirk Goldgar]

I have a query where I use the NZ function in the criteria to
reference a form field. If that form field is null, i want the query
to return ALL records. This almost works. Currently, if the form
field is null, it returns all records where the table field is NOT
null. I need those records containing the Null value to also be
returned though. Here is my function:

Like nz([Forms]![frmOrder].[txtOrderCategory],"*")


any suggestions?

You might do something like this:

SELECT * FROM YourTable
WHERE OrderCategory = [Forms]![frmOrder].[txtOrderCategory]
OR [Forms]![frmOrder].[txtOrderCategory] Is Null

 

[ghetto_banjo]

That did the trick, thanks Dirk.


I still don't really understand how the above code is interpreted in
Access to work the way it does, but hey, I have given up trying to
understand some of these things long ago.


Thanks again!

 

[John W. Vinson]

That did the trick, thanks Dirk.


I still don't really understand how the above code is interpreted in
Access to work the way it does, but hey, I have given up trying to
understand some of these things long ago.

This is actually worth going through, because the principle is widely
applicable:

SELECT * FROM YourTable
WHERE OrderCategory = [Forms]![frmOrder].[txtOrderCategory]
OR [Forms]![frmOrder].[txtOrderCategory] Is Null


A WHERE clause is a statement in Boolean (true/false) logic. If it evaluates
to TRUE for a record, that record is retrieved; if it evaluates to FALSE, it
isn't. For a simple example a WHERE clause like

WHERE OrderCategory = [Forms]![frmOrder].[txtOrderCategory]

is either true or not, depending on the value in the current record for
OrderCategory and for the form control. If they match the = operator returns
TRUE, if they don't, it returns FALSE.

In the more complex expression

WHERE OrderCategory = [Forms]![frmOrder].[txtOrderCategory]
OR [Forms]![frmOrder].[txtOrderCategory] Is Null

there are two evaluations made: first it compares the field to the value of
the textbox; the result might be TRUE or it might be FALSE depending on the
contents. It will be NULL (treated as FALSE) if txtOrderCategory is null,
since nothing is equal to NULL.

The second expression, though,

[Forms]![frmOrder].[txtOrderCategory] Is Null

will be TRUE if txtOrderCategory is in fact null.

When the entire expression is evaluated, FALSE OR TRUE = TRUE, by the way the
OR boolean operator works: "A OR B" is TRUE if A is true, B is true, or both
are true; it's only FALSE if both A and B are false. The AND operator returns
TRUE if and only if both A and B are true - if either one is false, so is the
result.

Therefore - if txtOrderCategory is null, then it doesn't make any difference
*what* the table value is; the WHERE clause will be TRUE and the record will
be retrieved.

 

[ghetto_banjo]

John,

Thank you very much for that explanation. I think my brain was too
hung up on how the query criteria was telling it to grab Null values
but now I see it really is as simple as the boolean expressions.

Thanks again, it was a good explanation.