Using iif Null and 0

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

luscioussarita - 12 Mar 2007 16:55 GMT

Hi Everyone,

I am kinda stuck on this statement. I want to check two fields within the
report using an iif statement. I want it to see if both fields are null or 0
then to move it up one. I have this already set for add and shrink. If not
I have to go into the table and edit the table for 0's all the time. I got
this to work using one field but I can't get it to work comparing two. When
I compare it to one field the ClientName disappears because it looking for
one value not two. Any suggestions?

Sarita

Example:

ClientName 2005 Budget 2006
------------- ------ --------- ------
Company1 5,000 $0
Company2 5,000 $0

This is how it shows:

ClientName 2005 Budget 2006
------------- ------ --------- ------
5,000 $0
5,000 $0

Original statement:
=IIf(Sum([2006])=0,Null+[ClientName],([ClientName]))
=IIf(Sum([2005])=0,Null+Sum([2005]),Sum([2005]))
=IIf(Sum([2006])=0,Null+Sum([2006]),Sum([2006]))

I tried this and it didn't work:
=IIf(Sum([2006] And [2005])=0,Null+[ClientName],([ClientName]))
=IIf(Sum([2005] And [2005])=0,Null+Sum([2005]),Sum([2005]))
=IIf(Sum([2006] And [2005])=0,Null+Sum([2006]),Sum([2006]))

Reply to this Message

Ofer Cohen - 12 Mar 2007 19:17 GMT

Try something like

IIf(Sum(Nz([2006],0)) + Sum(Nz([2005],0)) = 0 ,Null,[ClientName])

The Nz used to replace the Null with 0

Signature

Good Luck
BS"D

> Hi Everyone,
>
[quoted text clipped - 31 lines]
> =IIf(Sum([2005] And [2005])=0,Null+Sum([2005]),Sum([2005]))
> =IIf(Sum([2006] And [2005])=0,Null+Sum([2006]),Sum([2006]))

Reply to this Message

luscioussarita - 12 Mar 2007 22:15 GMT

Hi Ofer,

I am still having the same problem. Even if the number within anyone of the
fields is 0 or Null and the other two have a number I need that number and/or
client Name to show. If all three of them have $0 then the whole line
disappears which works great but not the rest.

ClientName 2005 Budget 2006
------------- ------ --------- ------
Company1 5,000 0
Company2 5,000 0
Company3 5,000 5,000

=IIf(Sum(Nz([Act06comm],0)),IIf(Sum(Nz([TtlRetRevAccm],0)),IIf(Sum(Nz([2006comm],0))=0,+[ClientName],[ClientName])))

I tried it your way with the + but if both 0's or Nulls were present even if
the budget had a number it took out the line.

Sarita

> Try something like
>
[quoted text clipped - 37 lines]
> > =IIf(Sum([2005] And [2005])=0,Null+Sum([2005]),Sum([2005]))
> > =IIf(Sum([2006] And [2005])=0,Null+Sum([2006]),Sum([2006]))

Reply to this Message

Ofer Cohen - 12 Mar 2007 22:36 GMT

Try

IIf(Sum(Nz([Act06comm],0))>0 Or Sum(Nz([TtlRetRevAccm],0)) > 0 Or
Sum(Nz([2006comm],0))>0, [ClientName], Null)

Signature

Good Luck
BS"D

> Hi Ofer,
>
[quoted text clipped - 57 lines]
> > > =IIf(Sum([2005] And [2005])=0,Null+Sum([2005]),Sum([2005]))
> > > =IIf(Sum([2006] And [2005])=0,Null+Sum([2006]),Sum([2006]))

Reply to this Message

luscioussarita - 13 Mar 2007 15:11 GMT

It worked perfectly! I had to adjust for negatives and I got my statement to
work. Thank you for all your help!!!

Sarita

> Try
>
[quoted text clipped - 62 lines]
> > > > =IIf(Sum([2005] And [2005])=0,Null+Sum([2005]),Sum([2005]))
> > > > =IIf(Sum([2006] And [2005])=0,Null+Sum([2006]),Sum([2006]))

Reply to this Message

luscioussarita - 13 Mar 2007 15:26 GMT

Thank you this worked perfectly. I had to adjust for the negative numbers
but it worked. You are an angel.

Sarita

> Try
>
[quoted text clipped - 62 lines]
> > > > =IIf(Sum([2005] And [2005])=0,Null+Sum([2005]),Sum([2005]))
> > > > =IIf(Sum([2006] And [2005])=0,Null+Sum([2006]),Sum([2006]))

Reply to this Message