IIF Statement Issue

G

Guest

The code I am reviewing contains a nested IIf statement but does not test for
a condition in the outer IIf:

In the example: IIF(IIF(Field = "A","Display X","Display Y"),"Test1")

Test1 is always displayed. Is there any use that this outer IIF with no
test expression serves ?
 
G

Guest

There is no condition for the first IIF
IIF(IIF(Field = "A","Display X","Display Y"),"Test1")
========================================
Should change to something like
IIf(condition, then,else)
or
IIf(condition,then,iif(condition,then,else))

The else from the first iif become another iif
 
G

Guest

Ofer,

I understand what you are saying, but the code I am reading, which I did not
write does have an IIF(IFF( without another condition. I do not have the
code here, so I typed a simplified example for his complex statement. My
question was, could there be any practical use for such an expression ? Is
there maybee some quirk in the IIF command that I do not know about ? I also
noticed the programmer instead of typing a hardcoded value to display, typed
a logic statement ie:
IIF(IIF(Condition,"Display Value",FieldA = FieldB))
 
G

Guest

You've got the syntax misplaced. It works, but what you're asking is:

If what's inside these parentheses is true, assign "Test1." If it's false,
don't assign anything.

IIF(Field = "A","Display X","Display Y") evaluates to TRUE. Therefore,
Test1 is assigned.

What are you really after? Something like this:

IIF(Field = "A","Display X", IIF(Field = "B", "Display Y", "Test1"))

What this statement means is that if Field = A, then assign "Display X."
However, if Field = B, then assign "Display Y." If Field = anything else
besides these two choices, assign "Test1."

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
G

Guest

I'm sorry, but there is no logic behind the IIf you provided us with.

Take this for example
If a=1 then
X = 10
Else
If a = 2 then
X = 15
Else
X = 30
End If
End If

Now, to change this If statement to Nested IIF, you get
X = IIf(a=1,10,iif(a=2,15,30))
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
 
C

Chris2

rmcompute said:
The code I am reviewing contains a nested IIf statement but does not test for
a condition in the outer IIf:

In the example: IIF(IIF(Field = "A","Display X","Display Y"),"Test1")

Test1 is always displayed. Is there any use that this outer IIF with no
test expression serves ?
Click to expand...

rmcompute,

That is very interesting.

I open the VBE (Tools > Macros > Visual Basic Editor). I open the
Immediate Window (View > Immediate Window).

I then paste into the immediate window, thus:

? IIF(IIF(Field = "A","Display X","Display Y"),"Test1")

And hit the enter key.

I get, "Compile Error: Argument not optional".

When I create a small sub and execute it:

Public Sub IIF_Error()

Debug.Print IIf(IIf("B" = "A", "Display X", "Display Y"), "Test1")

End Sub

I receive the same error.


I am curious how you got the above expression to return any value at
all. AFAICT, Access won't execute the outer IIF without a conditional
test in the first parameter (and with the inner IIF not returning
anything but "Display X" or "Display Y", there is no conditional test
in the first parameter of the outer IIF).

I'm running Access 2000 SP-3, JET 4.0 SP-6.


Sincerely,

Chris O.
 
G

Guest

Chris2,

Here is the actual code I was reviewing. It seems to be working, but what
would be the purpose of the outer IIF statement ?

pCB:
IIf(IIf([CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]<[CB]*-1.5,[CB]*-1.5,IIf([CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]>[CB]*1.5,[CB]*1.5,[CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]))/[cb]<0.7,0,IIf([CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]<[CB]*-1.5,[CB]*-1.5,IIf([CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]>[CB]*1.5,[CB]*1.5,[CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB])))
 
G

Guest

This code is fine, there is a criteria for the first iif, and it someting
like that

IIF(IIF(Field = "A","Display X","Display Y")<0.7,0,"Test1")

So the first iif compare the outcome of the second iif to 0.7 and if true it
returns 0 and if false it returns Test1.

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



rmcompute said:
Chris2,

Here is the actual code I was reviewing. It seems to be working, but what
would be the purpose of the outer IIF statement ?

pCB:
IIf(IIf([CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]<[CB]*-1.5,[CB]*-1.5,IIf([CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]>[CB]*1.5,[CB]*1.5,[CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]))/[cb]<0.7,0,IIf([CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]<[CB]*-1.5,[CB]*-1.5,IIf([CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB]>[CB]*1.5,[CB]*1.5,[CB]-([CB]*([CallBackPercentage]-[WCB]))/[WCB])))

Chris2 said:
rmcompute,

That is very interesting.

I open the VBE (Tools > Macros > Visual Basic Editor). I open the
Immediate Window (View > Immediate Window).

I then paste into the immediate window, thus:

? IIF(IIF(Field = "A","Display X","Display Y"),"Test1")

And hit the enter key.

I get, "Compile Error: Argument not optional".

When I create a small sub and execute it:

Public Sub IIF_Error()

Debug.Print IIf(IIf("B" = "A", "Display X", "Display Y"), "Test1")

End Sub

I receive the same error.


I am curious how you got the above expression to return any value at
all. AFAICT, Access won't execute the outer IIF without a conditional
test in the first parameter (and with the inner IIF not returning
anything but "Display X" or "Display Y", there is no conditional test
in the first parameter of the outer IIF).

I'm running Access 2000 SP-3, JET 4.0 SP-6.


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
G

Guest

Thank you.

Ofer said:
There is no condition for the first IIF
IIF(IIF(Field = "A","Display X","Display Y"),"Test1")
========================================
Should change to something like
IIf(condition, then,else)
or
IIf(condition,then,iif(condition,then,else))

The else from the first iif become another iif
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
Click to expand...
 
C

Chris2

Ofer said:
This code is fine, there is a criteria for the first iif, and it someting
like that

IIF(IIF(Field = "A","Display X","Display Y")<0.7,0,"Test1")

So the first iif compare the outcome of the second iif to 0.7 and if true it
returns 0 and if false it returns Test1.

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
Click to expand...

Ofer,

Yes, now that I can see the original code snippet, I can see that the
first parameter of the outer IIF does have a conditional test.


From the OP's first post:

"In the example: IIF(IIF(Field = "A","Display X","Display Y"),"Test1")

Test1 is always displayed. Is there any use that this outer IIF with
no
test expression serves ?"

I took the whole function, and took out the [] and added some _ so I
could put it into a VBA fuction.

Public Sub BigIIfTest()

Dim CB As Double
Dim WCB As Double
Dim CallBackPercentage As Double
Dim TestResults As Double

WCB = 0.1
CB = 5
CallBackPercentage = 0.1

TestResults = _
IIf( _
IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB < CB * -1.5 _
, CB * -1.5 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB > CB *
1.5 _
, CB * 1.5 _
, CB - (CB * (CallBackPercentage - WCB)) / WCB _
) _
) / CB < 0.7 _
, 0 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB < CB * -1.5
_
, CB * -1.5 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB > CB *
1.5 _
, CB * 1.5 _
, CB - (CB * (CallBackPercentage - WCB)) / WCB _
) _
) _
)

Debug.Print TestResults

End Sub


When CallBackPercentage is .1, the function returns 5.

When CallBackPerecentage is .5, the function returns 0.

This means that the function does not always return one value.


Ofer has located the test in the outer IIf first parameter, and the
above shows that the function can return more than one value with
varying input.

(Note: Yes, I am aware that the data I used isn't the same as the OP's
data,


Sincerely,

Chris O.
 
G

Guest

Thank you.

Chris2 said:
Ofer said:
This code is fine, there is a criteria for the first iif, and it someting
like that

IIF(IIF(Field = "A","Display X","Display Y")<0.7,0,"Test1")

So the first iif compare the outcome of the second iif to 0.7 and if true it
returns 0 and if false it returns Test1.

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
Click to expand...

Ofer,

Yes, now that I can see the original code snippet, I can see that the
first parameter of the outer IIF does have a conditional test.


From the OP's first post:

"In the example: IIF(IIF(Field = "A","Display X","Display Y"),"Test1")

Test1 is always displayed. Is there any use that this outer IIF with
no
test expression serves ?"

I took the whole function, and took out the [] and added some _ so I
could put it into a VBA fuction.

Public Sub BigIIfTest()

Dim CB As Double
Dim WCB As Double
Dim CallBackPercentage As Double
Dim TestResults As Double

WCB = 0.1
CB = 5
CallBackPercentage = 0.1

TestResults = _
IIf( _
IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB < CB * -1.5 _
, CB * -1.5 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB > CB *
1.5 _
, CB * 1.5 _
, CB - (CB * (CallBackPercentage - WCB)) / WCB _
) _
) / CB < 0.7 _
, 0 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB < CB * -1.5
_
, CB * -1.5 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB > CB *
1.5 _
, CB * 1.5 _
, CB - (CB * (CallBackPercentage - WCB)) / WCB _
) _
) _
)

Debug.Print TestResults

End Sub


When CallBackPercentage is .1, the function returns 5.

When CallBackPerecentage is .5, the function returns 0.

This means that the function does not always return one value.


Ofer has located the test in the outer IIf first parameter, and the
above shows that the function can return more than one value with
varying input.

(Note: Yes, I am aware that the data I used isn't the same as the OP's
data,


Sincerely,

Chris O.
Click to expand...
 
G

Guest

Any time, long iif statment can be very confusing some times, espaecially if
they are not yours
Good luck with your project
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



rmcompute said:
Thank you.

Chris2 said:
Ofer said:
This code is fine, there is a criteria for the first iif, and it someting
like that

IIF(IIF(Field = "A","Display X","Display Y")<0.7,0,"Test1")

So the first iif compare the outcome of the second iif to 0.7 and if true it
returns 0 and if false it returns Test1.

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
Click to expand...

Ofer,

Yes, now that I can see the original code snippet, I can see that the
first parameter of the outer IIF does have a conditional test.


From the OP's first post:

"In the example: IIF(IIF(Field = "A","Display X","Display Y"),"Test1")

Test1 is always displayed. Is there any use that this outer IIF with
no
test expression serves ?"

I took the whole function, and took out the [] and added some _ so I
could put it into a VBA fuction.

Public Sub BigIIfTest()

Dim CB As Double
Dim WCB As Double
Dim CallBackPercentage As Double
Dim TestResults As Double

WCB = 0.1
CB = 5
CallBackPercentage = 0.1

TestResults = _
IIf( _
IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB < CB * -1.5 _
, CB * -1.5 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB > CB *
1.5 _
, CB * 1.5 _
, CB - (CB * (CallBackPercentage - WCB)) / WCB _
) _
) / CB < 0.7 _
, 0 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB < CB * -1.5
_
, CB * -1.5 _
, IIf(CB - (CB * (CallBackPercentage - WCB)) / WCB > CB *
1.5 _
, CB * 1.5 _
, CB - (CB * (CallBackPercentage - WCB)) / WCB _
) _
) _
)

Debug.Print TestResults

End Sub


When CallBackPercentage is .1, the function returns 5.

When CallBackPerecentage is .5, the function returns 0.

This means that the function does not always return one value.


Ofer has located the test in the outer IIf first parameter, and the
above shows that the function can return more than one value with
varying input.

(Note: Yes, I am aware that the data I used isn't the same as the OP's
data,


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
G

Guest

I have a similar problem, I need the IIF(field length to equal 12, else show
value from another field. (ie iif([field1]length = 12, show field1, else
show [field2])

Can you help me write this query?
 
F

fredg

I have a similar problem, I need the IIF(field length to equal 12, else show
value from another field. (ie iif([field1]length = 12, show field1, else
show [field2])

Can you help me write this query?

Ofer said:
There is no condition for the first IIF
IIF(IIF(Field = "A","Display X","Display Y"),"Test1")
========================================
Should change to something like
IIf(condition, then,else)
or
IIf(condition,then,iif(condition,then,else))

The else from the first iif become another iif
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
Click to expand...
Click to expand...

When you refer to [Field] length = 12 do you mean the number of
characters in the field?

Exp:IIF(Len([FieldName]) = 12, [field1],[field2])
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nested IIf stops after two conditions 8
Nested IIF Statement syntax(?) problem 4
iif statement what is wrong with this expression 2
Time in calculated field for shifts going past midnight 0
Nested If conversion to Select case 5
IIF Statement not working as expected 3
iif, or, else? 3
Weird Query Possibility 1

Top