Not "String" wont show null

[vapid2323]

Ok I have a simple SQL query question.

SELECT tblSMRForm.CancelledReason, tblSMRForm.COI
FROM tblSMRForm
WHERE ((Not (tblSMRForm.CancelledReason)="Collection successful"));

This will prevent any records that have "Collection successful" from showing up but for some reason its also not showing the NULL values in that same field...

So I dont want to see the "Collection successful" records but i DO want to see the NULLs

What am I missing?

 

[Rabbie]

TRy this

SELECT tblSMRForm.CancelledReason, tblSMRForm.COI
FROM tblSMRForm
WHERE (tblSMRForm.CancelledReason is null) or ((Not (tblSMRForm.CancelledReason)="Collection successful"));

 

[boblarson]

And just to explain Rabbie's response -

If you are using NOT something=something then there must be a value to check. Nulls are not values so they would be excluded unless you explicitly include them back.

 

[vapid2323]

Thanks Rabbie and Bob!

 

[Rabbie]

Happy to have been of help