Access SQL: Not Equals on Null Text Field

Question

I have this bit of code:

UPDATE NewMaster SET NewMaster.[Risk Mitigation Status] = "Pending Audit/Assist"
WHERE (NOT (NewMaster.[Risk Mitigation Status] = "Complete"));

This updates all rows to "Complete", except "Pending Audit/Assist" (expected) and NULLs (for me, completely unexpected!) Same with <> operator. Do I just need to add OR IS NULL? This makes no sense to me!

Answer 1

NULLs are tricky like that. You have to explicitly test for, or otherwise escape, them. Trying to compare, convert or otherwise use them together with any non-null value will pretty much always break something.

I'm not practised with Access SQL, but based on this documentation, it looks like the proper function for escaping them in Access SQL is 'Nz'. As in:

WHERE (NOT (Nz(NewMaster.[Risk Mitigation Status],"0") = "Complete"));

That will substitute the value "0" for any nulls it finds, before comparing them to the string "Complete".