Results 1 to 6 of 6
  1. #1
    wp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    4

    nested iif statement in access with 2 conditions


    Hello,
    I am trying to write a nested IIf query which has two conditions and one of them being an AND:
    Here is my unsucessful attempt at the query:

    Assessment: IIf([CPT4]="1005F",1,IIf([CPT4]="2015F" And "2016F",1,0))

    In plain english, I am trying to return a 1 value when the CPT4 field = 1005F
    OR return a 1 value when the CPT4 field = 2015F AND 2016F

    so, I have mutiple rows for each patient and one row must have 2015F and another row for the same patient must have 2016F in order to return a 1.
    Hope this makes some sense.
    Thanks a lot
    w

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I assume it would be either one or the other, not actually "2015F AND 2016F". Presuming so, does this work?

    Assessment: IIf([CPT4]="1005F" Or [CPT4]="2015F" Or [CPT4]="2016F",1,0)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    wp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    4
    Paul,
    Thanks for your interest.
    No, it would be "2015F AND 2016F" as the second variable, not one or the other.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's what I get for assuming.

    How about:

    Assessment: IIf([CPT4]="1005F" Or [CPT4]="2015F AND 2016F",1,0)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    wp1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    4
    unfortunately, it's only capturing the 1005F in my results , not the "2015F AND 2016F" part.
    Thanks again for trying...
    w

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post a db here with some sample data in it? That should work.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Nested If statement
    By Bellablue in forum Access
    Replies: 7
    Last Post: 10-09-2011, 12:00 PM
  2. iif statement with multiple conditions
    By ragsgold in forum Queries
    Replies: 7
    Last Post: 08-24-2011, 05:38 PM
  3. Nested IIf Statement
    By traquino98 in forum Queries
    Replies: 5
    Last Post: 06-11-2011, 10:56 AM
  4. Nested Iif statement help
    By Goodge12 in forum Queries
    Replies: 6
    Last Post: 09-21-2010, 11:45 AM
  5. Iff Statement Meeting 2 Conditions
    By newbie in forum Access
    Replies: 11
    Last Post: 06-11-2009, 11:59 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