I am Going CRAZY

[rm]

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

 

[John W. Vinson]

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com