Just making sure we revisit the above statement in red. The result of comparing Null to anything should also be Null.Using Null in ANY comparison is usually asking for trouble because the following expression is TRUE in Access: Null <> Null
Null cannot be compared to anything including another null because WHATEVER the math, they are not equal to each other. Null trips up math expressions so fast it will make your head spin. You can TEST for Null with IsNull(X) which is TRUE or FALSE, or you can do an on-the-fly substitution using the NZ(X,default) function. Don't use Null as a meaningful value if the meaning is ANYTHING other than "no value is available." Assigning an interpretation to null other than that invites trouble in the form of sloppy programming.
This thread discusses use of nulls at great (and eventually boring) length:
https://access-programmers.co.uk/forums/showthread.php?t=160065
the following expression is TRUE in Access: Null <> Null
SELECT a, b, IsNull([a]) AS a_Is_Null, IsNull([b]) AS b_Is_Null, IIf([a]=[b],True,False) AS [a=b], IIf([a]<>[b],True,False) AS [a<>b]
FROM TestTable;
Thanks for trying to clarify, but I will say it again. Comparing anything to Null should result in Null - not True and not False either, just Null. Please try the following query and let us know the result:I think Doc just mistyped that. His overall message is "don't trust Null", which is correct.
Null <> Null resolves to False in Access:
TestTable:
id, a, b
1, 1, 1
2, 1, Null
3, Null, Null
Results:Code:SELECT a, b, IsNull([a]) AS a_Is_Null, IsNull([b]) AS b_Is_Null, IIf([a]=[b],True,False) AS [a=b], IIf([a]<>[b],True,False) AS [a<>b] FROM TestTable;
id, a, b, a_Is_Null, b_Is_Null, a=b, a<>b
1, 1, 1, 0, 0, -1, 0
2, 1, Null, 0, -1, 0, 0
3, Null, Null, -1, -1, 0, 0
SELECT a, b, a=null, b=null, a=b, a<>null, b<>null, a<>b FROM TestTable
I am not sure what it does, but one of the options with a yes/no field is "triple-state".
I think the triple-state includes a null
eg
https://www.experts-exchange.com/articles/10740/Access-Forms-Triple-State-Checkboxes.html