Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2007
    Location
    Pittsburgh, --UNITED STATES of AMERICA--
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF statement syntax (2003)

    I have a query where I am comparing two tables - Augdata and Sepdata. I join the tables by a unique Id. The fields I am comparing have Y and N in them. Two fields I am comparing are SepHealth and AugHealth. I want a third column that I will call GAin/Loss/No Change. I want an IIF statement that says - IIF SepHealth = AugHealth then "No Change" else iif SepHealth = Y and AugHealth = N, "Gain" else iif SepHealth = N and AugHealth = Y, "Loss". Can someone help me with the correct syntax for my Gain/Loss/No Change column?

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

    Re: IIF statement syntax (2003)

    Try this:
    <code>
    =IIf([SepHealth]=[AugHealth],"No Change",IIf([SepHealth]="Y" And [AugHealth]="N","Gain","Loss"))
    </code>
    This assumes that there are no blanks.

  3. #3
    New Lounger
    Join Date
    Sep 2007
    Location
    Pittsburgh, --UNITED STATES of AMERICA--
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF statement syntax (2003)

    What if there are blanks. I am home so I am not sure about the data. How would I account for blanks?

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

    Re: IIF statement syntax (2003)

    What do you want to return if

    1) AugHealth = Y and SepHealth is blank
    2) AugHealth = N and SepHealth is blank
    3) AugHealth is blank and SepHealth = Y
    4) AugHealth is blank and SepHealth = N
    5) AugHealth and SepHealth are both blank

  5. #5
    New Lounger
    Join Date
    Sep 2007
    Location
    Pittsburgh, --UNITED STATES of AMERICA--
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF statement syntax (2003)

    1) AugHealth = Y and SepHealth is blank - Loss
    2) AugHealth = N and SepHealth is blank - Loss
    3) AugHealth is blank and SepHealth = Y - Gain
    4) AugHealth is blank and SepHealth = N - Loss
    5) AugHealth and SepHealth are both blank - No change

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

    Re: IIF statement syntax (2003)

    Let's see. Try this expression:
    <code>
    IIf(IsNull([AugHealth]) And IsNull([SepHealth]) Or [AugHealth]=[SepHealth],"No Change",IIf([SepHealth]="Y","Gain","Loss"))
    </code>
    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  7. #7
    New Lounger
    Join Date
    Sep 2007
    Location
    Pittsburgh, --UNITED STATES of AMERICA--
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF statement syntax (2003)

    Thanks Hans - I will let you tomorrow if it works.

Posting Permissions

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