|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 24th, 2009, 07:26 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Help with NESTED IIF Statement in Access
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]*120),([NBV122008]-[current]*12),[NBV122008]))) 
|
|
#2
April 24th, 2009, 08:53 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Help with NESTED IIF Statement in Access
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]*120), ([NBV122008]-[current]*12),[NBV122008]))) I removed the 'and' from this part -- [Accum]0),([NBV122008]-[current]*12),0, and IIf(([NBV122008]-[current]*120),([NBV122008]-[ "AccessBeginner" wrote: 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]*120),([NBV122008]-[current]*12),[NBV122008])))
|
|
#3
April 25th, 2009, 12:27 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Help with NESTED IIF Statement in Access
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]*120), ([NBV122008]-[current]*12),[NBV122008])) hth "KARL DEWEY" wrote in message ... 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]*120), ([NBV122008]-[current]*12),[NBV122008]))) I removed the 'and' from this part -- [Accum]0),([NBV122008]-[current]*12),0, and IIf(([NBV122008]-[current]*120),([NBV122008]-[ "AccessBeginner" wrote: 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]*120),([NBV122008]-[current]*12),[NBV122008])))
|
|
#4
April 25th, 2009, 05:38 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Help with NESTED IIF Statement in Access
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]*120), ([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]*120), false - - test ([NBV122008]-[current]*12), true [NBV122008]))) false "tina" wrote: 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]*120), ([NBV122008]-[current]*12),[NBV122008])) hth "KARL DEWEY" wrote in message ... 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]*120), ([NBV122008]-[current]*12),[NBV122008]))) I removed the 'and' from this part -- [Accum]0),([NBV122008]-[current]*12),0, and IIf(([NBV122008]-[current]*120),([NBV122008]-[ "AccessBeginner" wrote: 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]*120),([NBV122008]-[current]*12),[NBV122008])))
|
|
#5
April 25th, 2009, 06:43 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Help with NESTED IIF Statement in Access
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]*120,[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 "AccessBeginner" wrote in message ... 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]*120),([NBV122008]-[current]*12),[NBV122008])))
|
|
#6
April 25th, 2009, 08:07 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Help with NESTED IIF Statement in Access
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) "AccessBeginner" wrote in message ... 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]*120),([NBV122008]-[current]*12),[NBV122008])))
|
|
#7
April 25th, 2009, 08:12 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Help with NESTED IIF Statement in Access
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" wrote in message ... 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]*120), ([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]*120), false - - test ([NBV122008]-[current]*12), true [NBV122008]))) false "tina" wrote: 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]*120), ([NBV122008]-[current]*12),[NBV122008])) hth "KARL DEWEY" wrote in message ... 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]*120), ([NBV122008]-[current]*12),[NBV122008]))) I removed the 'and' from this part -- [Accum]0),([NBV122008]-[current]*12),0, and IIf(([NBV122008]-[current]*120),([NBV122008]-[ "AccessBeginner" wrote: 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]*120),([NBV122008]-[current]*12),[NBV122008])))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
