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.
