Results 1 to 7 of 7
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    records w/null


    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?

  2. #2
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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

  3. #3
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Iif( something <> this AND Something <> Null, Iif(SomethingElse <> that AND somthingElse <> Null,"True","False"),"False")

    Iif(Test,TruePart,FalsePart)

    Dale

  4. #4
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    rzw0wr - did you really mean to code this test -> "something <> NULL"

    If so, it's really time for a beer.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Dal Jeanis View Post
    rzw0wr - did you really mean to code this test -> "something <> NULL"

    If so, it's really time for a beer.
    Or two. I'll even join you both.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    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

  7. #7
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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
    Code:
    (Fieldname <> Null)
    in VBA, this test should be coded
    Code:
    (NOT IsNull(Fieldname))
    In SQL, this test should be coded
    Code:
    (Fieldname Is NOT Null)
    In an IIF, the first will work, and the second might also.

    By the way, the far outside parenthesis in each example are optional.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Show null values in records
    By Nola-Edu in forum Queries
    Replies: 1
    Last Post: 04-10-2013, 12:36 PM
  2. Replies: 8
    Last Post: 08-13-2012, 04:30 AM
  3. Replies: 8
    Last Post: 06-13-2012, 08:08 PM
  4. Do not show null records
    By brobb56 in forum Forms
    Replies: 1
    Last Post: 09-23-2011, 02:29 PM
  5. Not showing records with null sum
    By eww in forum Queries
    Replies: 3
    Last Post: 04-04-2011, 03:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums