Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF Statement with multiple criteria (Access 97)

    Hello!

    Hoping that someone can help me with this. I need an Iif statement that will return a 1 if "NO" is found in any of these fields :EL1a, CA1a, RD11a, CD15a, or PF7a. I can create an Iif statement for each one individually, and then another statement that combines all of my single statements, but wanted to check to see if there is a way to combine this initially. This is what I have (too much):
    Expr1: IIf([EL1a]="No",1,0)
    Expr2: IIf([CA1a]="No",1,0)
    Expr3: IIf([CD15a]="No",1,0)
    Expr4: IIf([RD11a]="No",1,0)
    Expr5: IIf([PF7a]="No",1,0)
    Expr6: IIf([Expr1] Or [Expr2] Or [Expr3] Or [Expr4] Or [Expr5]=1,1,0)

    Thanks for any assistance that you can provide.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF Statement with multiple criteria (Access 97)

    You could use
    <code>
    -([EL1a]="No" Or [CA1a]="No" Or [CD15a]="No" Or [RD11a]="No" Or [PF7a]="No")
    </code>
    The result of the expression between parentheses is either True = -1 or False = 0. The minus sign changes -1 to 1.

    Note: if EL1a etc. were Yes/No fields, you could use
    <code>
    1-([EL1a] And [CA1a] And [CD15a] And [RD11a] And [PF7a])</code>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement with multiple criteria (Access 97)

    IIF([EL1a]="No",1, IIF([CA1a]="No",1, IIF([RD11a]="No",1, IIF([CD15a]="No",1,IIF([PF7a]="No",1,0 ) ) ) ) )

    If I have all my ( , ), and " "'s in the right place, this should work.
    Richard

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement with multiple criteria (Access 97)

    Thanks Hans!!!

    That worked great.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement with multiple criteria (Access 97)

    Thanks Richard,

    That took care of the Null values that I found in some of the data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •