Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation help in a query (2003)

    I have the following calculation:
    UCCIGainLoss_NoChange: IIf([CurrentUCCI]="Y" And [PrevUCCI]="Y","Yes-No Change",IIf([CurrentUCCI]="N" And [PrevUCCI]="N","No Coverage",IIf([CurrentUCCI]="Y" And [PrevUCCI]="N","Gain","Loss")))

    I have a union query joining CurrentUCCI and PrevUCCI. My problem is if a record is on one and not on the other, I get a null instead of a "N". How can I add an or Null to the ones like IIf([CurrentUCCI]="Y" and [PrevUCCI]="N" or is Null? I need some syntax help. Anywhere there is a "N", I need an or is Null.

    Thanks for your help.

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

    Re: Calculation help in a query (2003)

    Try

    UCCIGainLoss_NoChange: IIf(Nz([CurrentUCCI],"N")="Y" And Nz([PrevUCCI],"N")="Y","Yes-No Change",IIf(Nz([CurrentUCCI],"N")="N" And Nz([PrevUCCI],"N")="N","No Coverage",IIf(Nz([CurrentUCCI],"N")="Y" And Nz([PrevUCCI],"N")="N","Gain","Loss")))

    The Nz function returns its first argument, but if that is null, it returns the second argument. Here, null values in CurrentUCCI and PrevUCCI are replaced with "N".

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation help in a query (2003)

    When there is a "N" for current and a blank for Prev it is coming out as a gain when it should be "No Coverage". If Current is "Y" and Prev is blank then that should be a gain. The Current is "Y" and Prev is blank is coming out OK as a Gain but when Current is N and Prev is blank it is coming out as a gain which it should be No Coverage

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation help in a query (2003)

    Sorry, my fault. It works. Thanks Hans.

Posting Permissions

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