# Thread: Calculation help in a query (2003)

1. ## 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.

2. ## 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. ## 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. ## 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
•