iff, then, else textbox help!!! revisited...

  • Thread starter Thread starter clawdogs
  • Start date Start date
C

clawdogs

I need help merging the following IIF statements on a form. Both work
seperately but I need them to work together. Both are seperat IIF
statements without else. Else can be anything or zero etc...

=IIf([NOVRATE]>1,([CUMPERCENTOVNOV])-1)*([EMPLOYEE DATA subform].Form!
TOVCOMP2)*([NOVRATE])+([EMPLOYEE DATA subform].Form!TOVCOMP2)

=IIf([NOVRATE]<1,([NOVRATE])*([EMPLOYEE DATA subform].Form!TOVCOMP2)*
([CUMPERCENTOVNOV])
 
Assuming NOVRATE is never 1, then the 2nd IIF is the else to the first IIF

=IIf([NOVRATE]>1,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA
subform].Form!TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA
subform].Form!TOVCOMP2,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA subform].Form!
TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA subform].Form!TOVCOMP2)
 
Assuming NOVRATE is never 1, then the 2nd IIF is the else to the first IIF

=IIf([NOVRATE]>1,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA
subform].Form!TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA
subform].Form!TOVCOMP2,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA subform].Form!
TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA subform].Form!TOVCOMP2)



[email protected] said:
I need help merging the following IIF statements on a form.  Both work
seperately but I need them to work together.  Both are seperat IIF
statements without else.  Else can be anything or zero etc...
Click to expand...
=IIf([NOVRATE]>1,([CUMPERCENTOVNOV])-1)*([EMPLOYEE DATA subform].Form!
TOVCOMP2)*([NOVRATE])+([EMPLOYEE DATA subform].Form!TOVCOMP2)
Click to expand...
=IIf([NOVRATE]<1,([NOVRATE])*([EMPLOYEE DATA subform].Form!TOVCOMP2)*
([CUMPERCENTOVNOV])- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That didn't work. Can someone help with maybe IIF THEN, IIF THEN,
ELSE?
 
It's hard to believe they worked in their original form considering that the
expressions have different numbers of opening and closing parentheses.

It looks as if the responder copies the same thing for the Then and the
Else. It should be something like:

=IIf([NOVRATE]>1,

([CUMPERCENTOVNOV] - 1) *
[EMPLOYEE DATA subform].Form!TOVCOMP2 *
[NOVRATE] +
[EMPLOYEE DATA subform].Form!TOVCOMP2,

[NOVRATE] *
[EMPLOYEE DATA subform].Form!TOVCOMP2 *
[CUMPERCENTOVNOV])

The spaces and line breaks are for clarity only.

There is no need for parentheses around field names. Parentheses other than
those required for the IIf function (or other functions, if you had used
them) are for grouping data as needed to perform the calculations.

Assuming NOVRATE is never 1, then the 2nd IIF is the else to the first IIF

=IIf([NOVRATE]>1,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA
subform].Form!TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA
subform].Form!TOVCOMP2,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA subform].Form!
TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA subform].Form!TOVCOMP2)



[email protected] said:
I need help merging the following IIF statements on a form. Both work
seperately but I need them to work together. Both are seperat IIF
statements without else. Else can be anything or zero etc...
Click to expand...
=IIf([NOVRATE]>1,([CUMPERCENTOVNOV])-1)*([EMPLOYEE DATA subform].Form!
TOVCOMP2)*([NOVRATE])+([EMPLOYEE DATA subform].Form!TOVCOMP2)
Click to expand...
=IIf([NOVRATE]<1,([NOVRATE])*([EMPLOYEE DATA subform].Form!TOVCOMP2)*
([CUMPERCENTOVNOV])- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That didn't work. Can someone help with maybe IIF THEN, IIF THEN,
ELSE?
 
Assuming NOVRATE is never 1, then the 2nd IIF is the else to the first IIF
Click to expand...
=IIf([NOVRATE]>1,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA
subform].Form!TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA
subform].Form!TOVCOMP2,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA subform].Form!
TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA subform].Form!TOVCOMP2)
Click to expand...
[email protected] said:
I need help merging the following IIF statements on a form.  Both work
seperately but I need them to work together.  Both are seperat IIF
statements without else.  Else can be anything or zero etc...
=IIf([NOVRATE]>1,([CUMPERCENTOVNOV])-1)*([EMPLOYEE DATA subform].Form!
TOVCOMP2)*([NOVRATE])+([EMPLOYEE DATA subform].Form!TOVCOMP2)
=IIf([NOVRATE]<1,([NOVRATE])*([EMPLOYEE DATA subform].Form!TOVCOMP2)*
([CUMPERCENTOVNOV])- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

That didn't work.  Can someone help with maybe IIF THEN, IIF THEN,
ELSE?- Hide quoted text -
Click to expand...
Sorry, the standard format for nesting is:
=IIF(Test,TrueResult,IIF(Test,TrueResult,FalseResult))
So, substituting ...

=IIf([NOVRATE]>1,([CUMPERCENTOVNOV])-1)*([EMPLOYEE DATA subform].Form!
TOVCOMP2)*([NOVRATE])+([EMPLOYEE DATA subform].Form!TOVCOMP2),IIf
([NOVRATE]<1,([NOVRATE])*([EMPLOYEE DATA subform].Form!TOVCOMP2)*
([CUMPERCENTOVNOV])))
 
The OP never stated what would happen if NOVRATE is exactly 1, but allowing
for that possibility is the only reason I can see for nesting two IIf
statements. If it is just a matter of testing whether it is >1 or not, a
single IIf statement will suffice. The mismatched parentheses in the OP's
original expressions are surely part of the problem. From what I can tell
the first expression has five each opening and closing parentheses, but they
are not arranged in a way that seems to make sense. The second expression
doesn't even have the same number of each.

Assuming NOVRATE is never 1, then the 2nd IIF is the else to the first
IIF
Click to expand...
=IIf([NOVRATE]>1,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA
subform].Form!TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA
subform].Form!TOVCOMP2,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA subform].Form!
TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA subform].Form!TOVCOMP2)
Click to expand...
[email protected] said:
I need help merging the following IIF statements on a form. Both work
seperately but I need them to work together. Both are seperat IIF
statements without else. Else can be anything or zero etc...
=IIf([NOVRATE]>1,([CUMPERCENTOVNOV])-1)*([EMPLOYEE DATA subform].Form!
TOVCOMP2)*([NOVRATE])+([EMPLOYEE DATA subform].Form!TOVCOMP2)
=IIf([NOVRATE]<1,([NOVRATE])*([EMPLOYEE DATA subform].Form!TOVCOMP2)*
([CUMPERCENTOVNOV])- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

That didn't work. Can someone help with maybe IIF THEN, IIF THEN,
ELSE?- Hide quoted text -
Click to expand...
Sorry, the standard format for nesting is:
=IIF(Test,TrueResult,IIF(Test,TrueResult,FalseResult))
So, substituting ...

=IIf([NOVRATE]>1,([CUMPERCENTOVNOV])-1)*([EMPLOYEE DATA subform].Form!
TOVCOMP2)*([NOVRATE])+([EMPLOYEE DATA subform].Form!TOVCOMP2),IIf
([NOVRATE]<1,([NOVRATE])*([EMPLOYEE DATA subform].Form!TOVCOMP2)*
([CUMPERCENTOVNOV])))
 
It's hard to believe they worked in their original form considering that the
expressions have different numbers of opening and closing parentheses.

It looks as if the responder copies the same thing for the Then and the
Else.  It should be something like:

=IIf([NOVRATE]>1,

    ([CUMPERCENTOVNOV] - 1) *
    [EMPLOYEE DATA subform].Form!TOVCOMP2 *
    [NOVRATE] +
    [EMPLOYEE DATA subform].Form!TOVCOMP2,

    [NOVRATE] *
    [EMPLOYEE DATA subform].Form!TOVCOMP2 *
    [CUMPERCENTOVNOV])

The spaces and line breaks are for clarity only.

There is no need for parentheses around field names.  Parentheses otherthan
those required for the IIf function (or other functions, if you had used
them) are for grouping data as needed to perform the calculations.


Assuming NOVRATE is never 1, then the 2nd IIF is the else to the first IIF
Click to expand...
=IIf([NOVRATE]>1,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA
subform].Form!TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA
subform].Form!TOVCOMP2,[CUMPERCENTOVNOV]-1*[EMPLOYEE DATA subform].Form!
TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA subform].Form!TOVCOMP2)
Click to expand...
[email protected] said:
I need help merging the following IIF statements on a form. Both work
seperately but I need them to work together. Both are seperat IIF
statements without else. Else can be anything or zero etc...
=IIf([NOVRATE]>1,([CUMPERCENTOVNOV])-1)*([EMPLOYEE DATA subform].Form!
TOVCOMP2)*([NOVRATE])+([EMPLOYEE DATA subform].Form!TOVCOMP2)
=IIf([NOVRATE]<1,([NOVRATE])*([EMPLOYEE DATA subform].Form!TOVCOMP2)*
([CUMPERCENTOVNOV])- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

That didn't work.  Can someone help with maybe IIF THEN, IIF THEN,
ELSE?- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for clarifying... I used your example with spaces as the
following and it seems to work:

=IIf([NOVRATE]>1,([CUMPERCENTOVNOV] - 1)*[EMPLOYEE DATA subform].Form!
TOVCOMP2*[NOVRATE]+[EMPLOYEE DATA subform].Form!TOVCOMP2,[NOVRATE]*
[EMPLOYEE DATA subform].Form!TOVCOMP2*[CUMPERCENTOVNOV])
 
Back
Top