form controls not really null??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

oraustin@hotmail.com - 04 Jan 2006 11:59 GMT

I have a list box whose value I explicitly set to null in code.
Then when I include the list box in a query criteria it fails to match
but if I replace the criteria with 'null' then it matches fine.
So the code (cleaned up from that written by the query builder) looks
like this and works fine
SELECT TechnicalCategory FROM tblPrintParts WHERE Description Is Null;

but the following doesn't
SELECT TechnicalCategory FROM tblPrintParts WHERE Description
=[Forms]![Form2]![lstLevel3]

however in code i have a little debug statement
IF ISNULL(lstLevel3) then msgbox "list is null"

This is trigered

:( help please

Reply to this Message

Allen Browne - 04 Jan 2006 12:13 GMT

The behavior you describe is by design.
Two nulls are not equal.
If you think of Null as meaning Unknown, that will make sense.
More detail in:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

You can fix the query to print all records when the list box is null like
this:
SELECT TechnicalCategory FROM tblPrintParts
WHERE ([Description] = [Forms]![Form2]![lstLevel3])
OR ([Forms]![Form2]![lstLevel3] Is Null);

If you wanted to only select the records where Description is null when the
list box value is null:
SELECT TechnicalCategory FROM tblPrintParts
WHERE ([Description] = [Forms]![Form2]![lstLevel3])
OR ([Forms]![Form2]![lstLevel3] Is Null AND [Description] Is Null);

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a list box whose value I explicitly set to null in code.
> Then when I include the list box in a query criteria it fails to match
[quoted text clipped - 12 lines]
> This is trigered
> :( help please

Reply to this Message

Anthony England - 04 Jan 2006 12:26 GMT

In the first you have Is Null and in the second you are using "=". You
cannot find records where the description "equals null". You either have to
treat null values differently from any type of string, or use the Nz
function which turns nulls into zero-length strings (by default).

Reply to this Message

oraustin@hotmail.com - 04 Jan 2006 12:48 GMT

fixed with what I felt is simplest way using Nz function on both the
value from the table and the value from the form - thanks

Reply to this Message