What on earth am I missing!!

SELECT T.M, T.P, T.Keep FROM T WHERE T.Keep NOT IN (NULL, SPACE(0),
"Y");
SELECT T.M, T.P, T.Keep FROM T WHERE T.Keep <> "Y";
SELECT T.M, T.P, T.Keep FROM T WHERE T.Keep NOT IN (NULL, "Y");

0 Records Returned:

SELECT T.M, T.P, T.Keep FROM T WHERE T.Keep = C;
1 Record Returned
M P Keep
8 X C

--------------------------------
Table T
--------------------------------
P M Keep
--------------------------------
A 1 Y
A 3
A 4
B 7 Y
B 9
B 6
C 2
H 5 Y
X 8 C

On Fri, 15 Jul 2011 05:44:21 -0700 (PDT), rm <(E-Mail Removed)> wrote:

>What on earth am I missing!!
>
>SELECT T.M, T.P, T.Keep FROM T WHERE T.Keep NOT IN (NULL, SPACE(0),
>"Y");
>SELECT T.M, T.P, T.Keep FROM T WHERE T.Keep <> "Y";
>SELECT T.M, T.P, T.Keep FROM T WHERE T.Keep NOT IN (NULL, "Y");
>
>0 Records Returned:
>
>SELECT T.M, T.P, T.Keep FROM T WHERE T.Keep = C;
>1 Record Returned
>M P Keep
>8 X C
>
>--------------------------------
>Table T
>--------------------------------
>P M Keep
>--------------------------------
>A 1 Y
>A 3
>A 4
>B 7 Y
>B 9
>B 6
>C 2
>H 5 Y
>X 8 C

NULL is a funny beast. Nothing is equal to NULL; nothing is UNEQUAL to Null;
any IN clause containing NULL will fail. The only criteria that work to test
the nullity of a field are IS NULL and IS NOT NULL.

Try

SELECT T.M, T.P, T.KEEP
FROM T
WHERE T.Keep IS NOT NULL
AND T.Keep NOT IN("", "Y");

The test for "" is only necessary if you have overridden the default of Allow
Zero Length Strings = No on the table properties of the field.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com