form controls not really null??

[oraustin]

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

[Allen Browne]

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);

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

<or******@hotmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

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

[Anthony England]

<or******@hotmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

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

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

[oraustin]

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