NZ - Replacing a null field with zero in a report

[Huddle]

I know I can make a null field in a report show a zero with the Nz function but I don't know how to place it in the formula below. A little help please.



=(Sum(IIf([FSL]=0,1,0))-[0InReview]-[0Final]-[0Waiver])

[pbaldy]

It would likely need to be placed around each field individually.

[ssanfu]

Maybe:

=Sum(IIf(nz([FSL],0)=0,1,0))-nz([0InReview],0)-nz([0Final],0)-nz([0Waiver],0)

Note: the first character of a name shouldn't be a number. Sometimes Access will choke with object names beginning with a number.

[Huddle]

Thanks for the info and the tip.

[Huddle]

OK I'm trying the same with the below and it is not working. Do you see my problem?

=Sum(IIf(Nz([FSL],0)=1,IIf(Nz([Schedule_Status],0)="In AC Review",1,IIf(Nz([Schedule_Status],0)="In DC Review",1,IIf(Nz([Schedule_Status],0)="In LESPM Review",1,0)))))

[ssanfu]

You are missing the 1 (TRUE value for the [FSL]=1 test). It should be

=Sum(IIf(Nz([FSL],0)=1,1, IIf(Nz([Schedule_Status],0)="In AC  Review",1,IIf(Nz([Schedule_Status],0)="In DC  Review",1,IIf(Nz([Schedule_Status],0)="In LESPM Review",1,0)))))

This is how I build nested IF() statements. You have 4 IIF() statements. I start out with the basic syntax:

IIF(Cond,True,False)

Then I add the second IIF() statement in the False argument:

IIF(Cond,True,IIF(Cond,True,False))

Then the 3rd and 4th statements:

IIF(Cond,True,IIF(Cond,True,IIF(Cond,True,IIF(Cond,True,False))))

Next, I start replacing the cond (condition) arguments:

IIF(Nz([FSL],0)=1,True,IIF(Cond,True,IIF(Cond,True,IIF(Cond,True,False))))

If Nz([FSL],0)=1 is TRUE, I want a 1 returned, so now replace the True part with a 1:

IIF(Nz([FSL],0)=1,1,IIF(Cond,True,IIF(Cond,True,IIF(Cond,True,False))))

The 2nd condition is comparing strings, so for the NZ() function I would replace a NULL with a empty string instead of a zero. So now replace the 2nd cond with:

IIF(Nz([FSL],0)=1,1,IIF(IIf(Nz([Schedule_Status],"")="In AC  Review",True,IIF(Cond,True,IIF(Cond,True,False))))

Replacing all of the arguments, you end up with:

IIF(Nz([FSL],0)=1,1,IIF(Nz([Schedule_Status],"")="In AC Review",1,IIF(Nz([Schedule_Status],"")="In DC Review",1,IIF(Nz([Schedule_Status],"")="In LESPM Review",1,0))))

And you want to use the Sum() function:

=Sum(IIF(Nz([FSL],0)=1,1,IIF(Nz([Schedule_Status],"")="In AC Review",1,IIF(Nz([Schedule_Status],"")="In DC Review",1,IIF(Nz([Schedule_Status],"")="In LESPM Review",1,0)))))

You have the formula you want with the correct number of parenthesis.

------------------
Unfortunately, your formula will return only a 1 or a zero. The Sum() function is useless. For example, if [FSL]=1 , a one will be returned. That is where it stops. The remaining IIF() functions (as the false value) will not be evaluated to return a value.

If you want to sum the results of the conditions, try:

=Sum(IIf(Nz([FSL], 0) = 1, 1, 0) + IIf(Nz([Schedule_Status], "") = "In AC Review", 1, 0) + IIf(Nz([Schedule_Status], "") = "In DC Review", 1, 0) + IIf(Nz([Schedule_Status], "") = "In LESPM Review", 1, 0))

Since 3 of the conditions are the same control name, you can also write it like this:

=Sum(IIF(Nz([FSL],0)=1,1,0) + IIF(Nz([Schedule_Status],"")="In AC Review" OR Nz([Schedule_Status],"")="In DC Review" OR Nz([Schedule_Status],"")="In LESPM Review",1,0))

Pretty long winded, just to tell you you are missing a 1....

[Huddle]

Wow that is a lot of good information unfortunately the TRUE value for the [FSL]=1 test is the next IIf statement. The logic is If the FSL =1 then If the status is AC Review make it 1, otherwise if the status is DC review make it 1, otherwise if status is LESPM Review make it 1, if FSL isn't 1 make it 0. So by my logic I'm not missing the 1 but maybe my formula is completely wrong.

[ssanfu]

unfortunately the TRUE value for the [FSL]=1 test is the next IIf statement

But then you are missing the FALSE argument.

If FSL =1 and the status is DC review, should the result be 1 or 2?

How about this:

=Sum(IIF(Nz([FSL],0)=1,IIF(Nz([Schedule_Status],"")="In AC Review" or Nz([Schedule_Status],"")="In DC Review" or Nz([Schedule_Status],"")="In LESPM Review",1,0),0))

[Huddle]

That works except there's an extra IIF at the front I just took out. Thanks so much.

[ssanfu]

Great!

Don't you love "cut and paste"???