IIF Statement using Is Null and is Not Null

[Guest]

BothFields: IIf([Remainingshares]="is
null",[Buysellshrs],IIf([Remainingshares]="is not null",[RemainingShares]))


What I am trying to do is

If Remaining Shares are BLANK show BUYSELLSHRS

If Remaining Shares are NOT BLANK Show Remaining shares

 

[BruceM]

There are a few difficulties here. There are only two possibilities for
[RemainingShares]: either it is blank or it is not. You don't need the
second IIf for just two possibilities. An IIf statement can be thought of
thus: If a condition is true, do something, otherwise do something else.
Another thing is that you are checking for the text "is null" and "is not
null". That's what the quotes are for. But you want to check whether the
field is blank.
With that in mind, the following may work.
BothFields: IIf([Remainingshares] is null,[Buysellshrs],[RemainingShares])
The other thing that needs mentioning is that Null is not the same as an
empty (zero-length) string, nor is it the same as 0. Is Null will not
necessarily produce the desired results. Instead of Is Null you could use =
"" or = 0, depending on what is actually in the Remainingshares field.

 

[Guest]

Thank you
That work great.

There are a few difficulties here. There are only two possibilities for
[RemainingShares]: either it is blank or it is not. You don't need the
second IIf for just two possibilities. An IIf statement can be thought of
thus: If a condition is true, do something, otherwise do something else.
Another thing is that you are checking for the text "is null" and "is not
null". That's what the quotes are for. But you want to check whether the
field is blank.
With that in mind, the following may work.
BothFields: IIf([Remainingshares] is null,[Buysellshrs],[RemainingShares])
The other thing that needs mentioning is that Null is not the same as an
empty (zero-length) string, nor is it the same as 0. Is Null will not
necessarily produce the desired results. Instead of Is Null you could use =
"" or = 0, depending on what is actually in the Remainingshares field.

BothFields: IIf([Remainingshares]="is
null",[Buysellshrs],IIf([Remainingshares]="is not
null",[RemainingShares]))


What I am trying to do is

If Remaining Shares are BLANK show BUYSELLSHRS

If Remaining Shares are NOT BLANK Show Remaining shares

 

[BruceM]

You're very welcome.

Thank you
That work great.

There are a few difficulties here. There are only two possibilities for
[RemainingShares]: either it is blank or it is not. You don't need the
second IIf for just two possibilities. An IIf statement can be thought
of
thus: If a condition is true, do something, otherwise do something else.
Another thing is that you are checking for the text "is null" and "is not
null". That's what the quotes are for. But you want to check whether
the
field is blank.
With that in mind, the following may work.
BothFields: IIf([Remainingshares] is
null,[Buysellshrs],[RemainingShares])
The other thing that needs mentioning is that Null is not the same as an
empty (zero-length) string, nor is it the same as 0. Is Null will not
necessarily produce the desired results. Instead of Is Null you could
use =
"" or = 0, depending on what is actually in the Remainingshares field.

BothFields: IIf([Remainingshares]="is
null",[Buysellshrs],IIf([Remainingshares]="is not
null",[RemainingShares]))


What I am trying to do is

If Remaining Shares are BLANK show BUYSELLSHRS

If Remaining Shares are NOT BLANK Show Remaining shares