IIf question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I write an Iif statement (or something else) that will add two numbers
when the first number is negative and the second is postive, but will also
subtract if the first number is greater than the second number.
 
Kim

What have you tried?

Have you checked Access/HELP on the IIF() syntax?

Have you covered all possibilities of the relationships between the numbers?
For example, if the first number is negative, but still larger than the
second, what should happen?

Good luck

Jeff Boyce
<Access MVP>
 
Yes, I've tried the Access Iif( ). I would like an "or" in the IIf statement
if possible (or something similar). I need to have Access to check to see if
the first number is negative or postive.

If the first number is negative - I need the second number subtracted from
the first number so I don't end up with a large negative result.

If the first number is postive - I need to have the first number subtracted
from the second and the end result to show as negative if the second number
is larger than the first.

I need to show if the inventory used has overspent the inventory bided
amount on a report footer.
 
Kim

You can "nest" IIF() statements to accomplish your "or". (see in-line
below)

Kim said:
Yes, I've tried the Access Iif( ). I would like an "or" in the IIf statement
if possible (or something similar). I need to have Access to check to see if
the first number is negative or postive.

If the first number is negative - I need the second number subtracted from
the first number so I don't end up with a large negative result.
Click to expand...

IIF([FirstNumber]<0, [FirstNumber] - [SecondNumber], ...)
If the first number is postive - I need to have the first number subtracted
from the second and the end result to show as negative if the second number
is larger than the first.
Click to expand...

IIF(([FirstNumber]<0), ([FirstNumber] - [SecondNumber]),
IIF(([SecondNumber]>[FirstNumber]),
(([SecondNumber]-[FirstNumber])*-1),([SecondNumber]-[FirstNumber]))

(I stuck in way too many extra parentheses, to help keep my thoughts
together)
 
Thanks!

Jeff Boyce said:
Kim

You can "nest" IIF() statements to accomplish your "or". (see in-line
below)

Kim said:
Yes, I've tried the Access Iif( ). I would like an "or" in the IIf statement
if possible (or something similar). I need to have Access to check to see if
the first number is negative or postive.

If the first number is negative - I need the second number subtracted from
the first number so I don't end up with a large negative result.
Click to expand...

IIF([FirstNumber]<0, [FirstNumber] - [SecondNumber], ...)
If the first number is postive - I need to have the first number subtracted
from the second and the end result to show as negative if the second number
is larger than the first.
Click to expand...

IIF(([FirstNumber]<0), ([FirstNumber] - [SecondNumber]),
IIF(([SecondNumber]>[FirstNumber]),
(([SecondNumber]-[FirstNumber])*-1),([SecondNumber]-[FirstNumber]))

(I stuck in way too many extra parentheses, to help keep my thoughts
together)


--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
 
Back
Top