Results 1 to 8 of 8
  1. #1
    timmygrover is offline Novice
    Windows Vista Access 2003
    Join Date
    Dec 2011
    Posts
    26

    IFF Statement returning the same result for true & false

    Okay, so I've built an IFF expression that is determined by a number of variables.



    If any of the Data fields are filled I want the statement to return true.

    If NONE of the four are filled in I want it to return false.

    As it is, it returns true no matter what.

    Here is the expression I'm using:
    Title: IIF (IsNull([Data1] and [Data2] and [Data3] and [Data4]), True , False)

    What could be wrong?

  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Use this IIF(IsNull([data1]) and Isnull([data2]) and isnull([data2]) and isnull([data3]) and isnull([data4]),True,False)

    They way you have it if any field is null and you and it with another field the result is null. So your statement will return True if any of the data fields are null not all.

  3. #3
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Oops added isnull([data2]) twice

  4. #4
    timmygrover is offline Novice
    Windows Vista Access 2003
    Join Date
    Dec 2011
    Posts
    26
    You sir, are a wonderful person and I love you.

  5. #5
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Timmy and Ray -

    I think the correction given by Ray is incorrect. It should read:

    IIF(IsNull([data1]) and isnull([data2]) and isnull([data3]) and isnull([data4]),False,True)

    This returns a False only if ALL 4 of the fields are Null, which is what Tim wants.

    John

  6. #6
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    John, the only difference between yours and mine is I accidentally typed the isnull([data2]) twice. Which I realized right after I posted and posted the Oops message immediately after correcting my mistake.

  7. #7
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Ray -

    Look again. Yours has "True, False" as the two return values. I changed it to "False, True" , which is what it should be.

    John

  8. #8
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Your right I re-read the original post and I did get it backwards good catch

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

Similar Threads

  1. SELECT "False" if any records are false?
    By Azurewrath in forum Queries
    Replies: 6
    Last Post: 12-21-2011, 03:36 PM
  2. IIF Statement Returning #Error
    By DrDefpoints in forum Queries
    Replies: 6
    Last Post: 05-26-2011, 12:25 PM
  3. result -1 in Iff statement
    By newtoAccess in forum Queries
    Replies: 10
    Last Post: 04-08-2011, 05:43 PM
  4. Yes/No True/False
    By DSTR3 in forum Access
    Replies: 5
    Last Post: 12-04-2010, 05:56 PM
  5. True or false
    By tleec in forum Queries
    Replies: 1
    Last Post: 02-01-2008, 10:41 AM

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