All using ms access 2010. I am using criteria: <>"Something" and <> "SomethingElse" in a query. It is working except it is also excluding if the field is null. I need to return the records if this particular field is null. How do I do this?
All using ms access 2010. I am using criteria: <>"Something" and <> "SomethingElse" in a query. It is working except it is also excluding if the field is null. I need to return the records if this particular field is null. How do I do this?
WHERE (Fieldname Is Null) OR (Fieldname <>"Something" AND FieldName <> "SomethingElse")
Here's a whole page of tips by Access MVP llen Browne about NULL. http://allenbrowne.com/casu-12.html
Iif( something <> this AND Something <> Null, Iif(SomethingElse <> that AND somthingElse <> Null,"True","False"),"False")
Iif(Test,TruePart,FalsePart)
Dale
rzw0wr - did you really mean to code this test -> "something <> NULL"
If so, it's really time for a beer.
I did write that.
I think someone else wrote that under my name..
Yes I wrote that thinking something else.
I write code as English then I am supposed to change it to code after I get the idea.
I was trying to remember how to say Not = null in an Iif() and got side tracked.
However No I did not mean to leave it like that in my post.
I will join you both in a beer and coffee.
Sorry.
Dale
Just to be clear for all the newbies, Nulls are weird. See http://allenbrowne.com/casu-12.html There are special syntaxes to write the test that in pseudocode reads as
in VBA, this test should be codedCode:(Fieldname <> Null)
In SQL, this test should be codedCode:(NOT IsNull(Fieldname))
In an IIF, the first will work, and the second might also.Code:(Fieldname Is NOT Null)
By the way, the far outside parenthesis in each example are optional.