Help with NESTED IIF Statement in Access

  • Thread starter Thread starter AccessBeginner
  • Start date Start date
A

AccessBeginner

CAn anyone please figure out wha't wrong with the query formula below? Thanks!

NBV122009: IIf(([Cost]<0 And
[Accum]<0),([NBV122008]-[current]*12),IIf(([Cost]>0 And
[Accum]>0),([NBV122008]-[current]*12),0, and
IIf(([NBV122008]-[current]*12<0),([NBV122008]-[current]*12),[NBV122008])))
 
This should work --
NBV122009: IIf(([Cost]<0 And [Accum]<0), ([NBV122008]-[current]*12),
IIf(([Cost]>0 And [Accum]>0), ([NBV122008]-[current]*12),0,
IIf(([NBV122008]-[current]*12<0), ([NBV122008]-[current]*12),[NBV122008])))

I removed the 'and' from this part --
[Accum]>0),([NBV122008]-[current]*12),0, and
IIf(([NBV122008]-[current]*12<0),([NBV122008]-[
 
will that work, Karl? looks like the third and final IIf() function is
actually a *fourth* argument of the 2nd IIf() function - i didn't see any
documentation of an optional fourth argument in the Help topic.

IIf(([Cost]>0 And [Accum]>0) , ([NBV122008]-[current]*12) , 0 ,
IIf(([NBV122008]-[current]*12<0), ([NBV122008]-[current]*12),[NBV122008]))

hth


KARL DEWEY said:
This should work --
NBV122009: IIf(([Cost]<0 And [Accum]<0), ([NBV122008]-[current]*12),
IIf(([Cost]>0 And [Accum]>0), ([NBV122008]-[current]*12),0,
IIf(([NBV122008]-[current]*12<0), ([NBV122008]-[current]*12),[NBV122008])))

I removed the 'and' from this part --
[Accum]>0),([NBV122008]-[current]*12),0, and
IIf(([NBV122008]-[current]*12<0),([NBV122008]-[

AccessBeginner said:
CAn anyone please figure out wha't wrong with the query formula below? Thanks!

NBV122009: IIf(([Cost]<0 And
[Accum]<0),([NBV122008]-[current]*12),IIf(([Cost]>0 And
[Accum]>0),([NBV122008]-[current]*12),0, and
Click to expand...
Click to expand...
IIf(([NBV122008]-[current]*12<0),([NBV122008]-[current]*12),[NBV122008])))
 
The 2nd IIF had 3 instead of 2.

NBV122009: IIf(([Cost]<0 And [Accum]<0), ([NBV122008]-[current]*12),
IIf(([Cost]>0 And [Accum]>0), ([NBV122008]-[current]*12),
IIf(([NBV122008]-[current]*12<0), ([NBV122008]-[current]*12),[NBV122008])))

This is how it breaks down ---
NBV122009: IIf(([Cost]<0 And [Accum]<0),
test
([NBV122008]-[current]*12),
true
IIf(([Cost]>0 And [Accum]>0),
false - - test
([NBV122008]-[current]*12),
true
IIf(([NBV122008]-[current]*12<0),
false - - test
([NBV122008]-[current]*12),
true
[NBV122008])))
false

tina said:
will that work, Karl? looks like the third and final IIf() function is
actually a *fourth* argument of the 2nd IIf() function - i didn't see any
documentation of an optional fourth argument in the Help topic.

IIf(([Cost]>0 And [Accum]>0) , ([NBV122008]-[current]*12) , 0 ,
IIf(([NBV122008]-[current]*12<0), ([NBV122008]-[current]*12),[NBV122008]))

hth


KARL DEWEY said:
This should work --
NBV122009: IIf(([Cost]<0 And [Accum]<0), ([NBV122008]-[current]*12),
IIf(([Cost]>0 And [Accum]>0), ([NBV122008]-[current]*12),0,
IIf(([NBV122008]-[current]*12<0), ([NBV122008]-[current]*12),[NBV122008])))

I removed the 'and' from this part --
[Accum]>0),([NBV122008]-[current]*12),0, and
IIf(([NBV122008]-[current]*12<0),([NBV122008]-[

AccessBeginner said:
CAn anyone please figure out wha't wrong with the query formula below? Thanks!

NBV122009: IIf(([Cost]<0 And
[Accum]<0),([NBV122008]-[current]*12),IIf(([Cost]>0 And
[Accum]>0),([NBV122008]-[current]*12),0, and
Click to expand...
Click to expand...
IIf(([NBV122008]-[current]*12<0),([NBV122008]-[current]*12),[NBV122008])))
Click to expand...
 
okay, i think Karl and i were both focused on the *syntax* of the nested
IIF() functions, not seeing the expression itself. the syntax IS wrong, but
i'm also having trouble understanding the logical sense of the expression as
you posted it. it says:

If Cost is less than zero and Accum is less than zero,
Then return the result of NBV122008 minus current times 12,
Otherwise If Cost is greater than zero and Accum is greater than zero,
Then return the result of NBV122008 minus current times 12,
Otherwise return zero.

And if NBV122008 minus current times 12 is less than zero,
Then return the result of NBV122008 minus current times 12,
Otherwise return NBV122008.

if the above logical statement is what you're aiming for, then i think the
following expression will provide it, as

IIf(([Cost]<0 And [Accum]<0) Or ([Cost]>0 And
[Accum]>0),IIf([NBV122008]-[current]*12<0,[NBV122008]-[current]*12,[NBV12200
8]), 0)

which says

If Cost and Accum are each less than zero OR Cost and Accum are each greater
than zero,
Then If NBV122008 minus current times 12 is less than zero,
Then return NBV122008 minus current times 12,
Otherwise return NBV122008,
Otherwise return zero.

if none of the posted expressions offered in this thread are hitting the
mark, then please explain what you're trying to accomplish, in logical
terms. try using sentences to state what logic you want to apply, rather
than writing it in an expression.

hth
 
If you would tell us how you intent to calculate your net book value, it
would be easier for us to understand what's wrong with your equation. By
taking a look at your expression, this is probably what you want to write:

IIf( ([Cost] < 0 And [Accum] < 0) OR ([Cost] > 0 And [Accum] > 0),
IIf( ([NBV122008]-[current]*12 < 0), ([NBV122008]-[current]*12),
[NBV122008]),
0)
)

However, I would be surprised if this will really compute the value that you
need or that you want.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
What the OP try to express if the notion that if the first two IIF
expressions are true, then they must return the value
([NBV122008]-[current]*12) but only at the condition that this value is
lesser than zero; otherwise, they must return the value of [NBV122008]
instead.

However, I'm not sure that even this is the correct way of calculating his
net book value for december 2009.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


KARL DEWEY said:
The 2nd IIF had 3 instead of 2.

NBV122009: IIf(([Cost]<0 And [Accum]<0), ([NBV122008]-[current]*12),
IIf(([Cost]>0 And [Accum]>0), ([NBV122008]-[current]*12),
IIf(([NBV122008]-[current]*12<0),
([NBV122008]-[current]*12),[NBV122008])))

This is how it breaks down ---
NBV122009: IIf(([Cost]<0 And [Accum]<0),
test
([NBV122008]-[current]*12),
true
IIf(([Cost]>0 And [Accum]>0),
false - - test
([NBV122008]-[current]*12),
true
IIf(([NBV122008]-[current]*12<0),
false - - test
([NBV122008]-[current]*12),
true
[NBV122008])))
false

tina said:
will that work, Karl? looks like the third and final IIf() function is
actually a *fourth* argument of the 2nd IIf() function - i didn't see any
documentation of an optional fourth argument in the Help topic.

IIf(([Cost]>0 And [Accum]>0) , ([NBV122008]-[current]*12) , 0 ,
IIf(([NBV122008]-[current]*12<0),
([NBV122008]-[current]*12),[NBV122008]))

hth


KARL DEWEY said:
This should work --
NBV122009: IIf(([Cost]<0 And [Accum]<0), ([NBV122008]-[current]*12),
IIf(([Cost]>0 And [Accum]>0), ([NBV122008]-[current]*12),0,
IIf(([NBV122008]-[current]*12<0), ([NBV122008]-[current]*12),[NBV122008])))

I removed the 'and' from this part --
[Accum]>0),([NBV122008]-[current]*12),0, and
IIf(([NBV122008]-[current]*12<0),([NBV122008]-[

:

CAn anyone please figure out wha't wrong with the query formula
below? Thanks!

NBV122009: IIf(([Cost]<0 And
[Accum]<0),([NBV122008]-[current]*12),IIf(([Cost]>0 And
[Accum]>0),([NBV122008]-[current]*12),0, and
Click to expand...
IIf(([NBV122008]-[current]*12<0),([NBV122008]-[current]*12),[NBV122008])))
Click to expand...
Click to expand...
 
Back
Top