iif statement

[Lisa C]

I am trying to create an iif statement and am having problems.

This is what I have:
=IIf(([Description]="A" And [UserFld3] Like
"I"),"B",[Description],IIF(([Description]="A" And [UserFld3] Like
"P"),"C",[Description]))

This is what I want the results to be:
If Description="A" and UserFld3 is like "I" then "B" else Description
if Description="A" and UserFld3 is like "P" then "C" else Description

Is my nesting wrong, do I need to move criteria around?

Thanks,
Lisa

 

[Ofer Cohen]

I assume that what you are looking for

If Description="A" and UserFld3 is like "I" then "B" else if Description="A"
and UserFld3 is like "P" then "C" else Description

Then try

=IIf([Description]="A" And [UserFld3] = "I","B",IIF([Description]="A" And
[UserFld3] = "P","C",[Description]))

 

[Todos Menos [MSFT]]

IIF is not ansi compliant

learn CASE WHEN THEN syntax; and use Access Data Projects

case when then is much much much much more powerful

 

[Lisa C]

Thanks.
You were right in the assumption, but it does not work as expected. It
works for the first criteria, but not the second. It does not return the
value of "C", but instead return the value of [Description].

I assume that what you are looking for

If Description="A" and UserFld3 is like "I" then "B" else if Description="A"
and UserFld3 is like "P" then "C" else Description

Then try

=IIf([Description]="A" And [UserFld3] = "I","B",IIF([Description]="A" And
[UserFld3] = "P","C",[Description]))



--
Good Luck
BS"D

I am trying to create an iif statement and am having problems.

This is what I have:
=IIf(([Description]="A" And [UserFld3] Like
"I"),"B",[Description],IIF(([Description]="A" And [UserFld3] Like
"P"),"C",[Description]))

This is what I want the results to be:
If Description="A" and UserFld3 is like "I" then "B" else Description
if Description="A" and UserFld3 is like "P" then "C" else Description

Is my nesting wrong, do I need to move criteria around?

Thanks,
Lisa

 

[Ofer Cohen]

In your example you used Like and not =, was there a reason for that?

This should ork, unless you are looking for a part of a field that start
with the leter P

=IIf([Description]="A" And [UserFld3] = "I","B",IIF([Description]="A" And
[UserFld3] Like "P*","C",[Description]))


--
Good Luck
BS"D

Thanks.
You were right in the assumption, but it does not work as expected. It
works for the first criteria, but not the second. It does not return the
value of "C", but instead return the value of [Description].

I assume that what you are looking for

If Description="A" and UserFld3 is like "I" then "B" else if Description="A"
and UserFld3 is like "P" then "C" else Description

Then try

=IIf([Description]="A" And [UserFld3] = "I","B",IIF([Description]="A" And
[UserFld3] = "P","C",[Description]))



--
Good Luck
BS"D

I am trying to create an iif statement and am having problems.

This is what I have:
=IIf(([Description]="A" And [UserFld3] Like
"I"),"B",[Description],IIF(([Description]="A" And [UserFld3] Like
"P"),"C",[Description]))

This is what I want the results to be:
If Description="A" and UserFld3 is like "I" then "B" else Description
if Description="A" and UserFld3 is like "P" then "C" else Description

Is my nesting wrong, do I need to move criteria around?

Thanks,
Lisa

 

[Tony Toews [MVP]]

Text that A a r o n K e m p f wrote snipped.

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm