Help with NESTED IIF Statement in Access

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

 

[KARL DEWEY]

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]-[

 

[tina]

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

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

IIf(([NBV122008]-[current]*12<0),([NBV122008]-[current]*12),[NBV122008])))

 

[KARL DEWEY]

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

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

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

IIf(([NBV122008]-[current]*12<0),([NBV122008]-[current]*12),[NBV122008])))

 

[tina]

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

 

[Sylvain Lafontaine]

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)

 

[Sylvain Lafontaine]

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)

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

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

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

IIf(([NBV122008]-[current]*12<0),([NBV122008]-[current]*12),[NBV122008])))