Using iif Null and 0

G

Guest

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]))
 
G

Guest

Try something like

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

The Nz used to replace the Null with 0
 
G

Guest

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

Ofer Cohen said:
Try something like

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

The Nz used to replace the Null with 0

--
Good Luck
BS"D


luscioussarita said:
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]))
Click to expand...
Click to expand...
 
G

Guest

Try

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


--
Good Luck
BS"D


luscioussarita said:
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

Ofer Cohen said:
Try something like

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

The Nz used to replace the Null with 0

--
Good Luck
BS"D


luscioussarita said:
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]))
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Sarita


Ofer Cohen said:
Try

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


--
Good Luck
BS"D


luscioussarita said:
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

Ofer Cohen said:
Try something like

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

The Nz used to replace the Null with 0

--
Good Luck
BS"D


:

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]))
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Sarita

Ofer Cohen said:
Try

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


--
Good Luck
BS"D


luscioussarita said:
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

Ofer Cohen said:
Try something like

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

The Nz used to replace the Null with 0

--
Good Luck
BS"D


:

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]))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum a field with null value 5
Access Report Average Miles/Gallon in Group Footer and Report Footer? 9
Complex Query not working on report 2
#Num! division with IIf statements 6
Aggregate Function Error 0
SynNeed to account for null values in subreports 2
IIF FUNCTION and multiple values 2
If Then Else Statment in Report 4

Top