Problems with IIF

[mattc66 via AccessMonster.com]

Okay I am going bald pulling my hair out. I have tried the below two options
and neither result works.

If company = C1 then display WDNVL and etc.

What am I doing wrong?

=IIf([COMPANY]="C1","WDNVL") Or ([COMPANY]="C2","COKATO") Or ([COMPANY]="C3",
"NOVI")

=IIf([COMPANY]="C1","WDNVL") Or IIF([COMPANY]="C2","COKATO") _
Or IIF([COMPANY]="C3","NOVI")

 

[KARL DEWEY]

Try this ---
=IIf([COMPANY]="C1","WDNVL", IIF([COMPANY]="C2","COKATO", IIF([COMPANY]
="C3", "NOVI", "Error")))

 

[mattc66 via AccessMonster.com]

I still get #Error.

Try this ---
=IIf([COMPANY]="C1","WDNVL", IIF([COMPANY]="C2","COKATO", IIF([COMPANY]
="C3", "NOVI", "Error")))

Okay I am going bald pulling my hair out. I have tried the below two options
and neither result works.

[quoted text clipped - 8 lines]

=IIf([COMPANY]="C1","WDNVL") Or IIF([COMPANY]="C2","COKATO") _
Or IIF([COMPANY]="C3","NOVI")

 

[mattc66 via AccessMonster.com]

I got it using your suggestion and changing the name of the control to
xCompany

I still get #Error.

Try this ---
=IIf([COMPANY]="C1","WDNVL", IIF([COMPANY]="C2","COKATO", IIF([COMPANY]

[quoted text clipped - 5 lines]

=IIf([COMPANY]="C1","WDNVL") Or IIF([COMPANY]="C2","COKATO") _
Or IIF([COMPANY]="C3","NOVI")

 

[KARL DEWEY]

You did not say what the error was.

How can anyone guess as to what to do or try without knowing what the error
statement was?

I got it using your suggestion and changing the name of the control to
xCompany

I still get #Error.

Try this ---
=IIf([COMPANY]="C1","WDNVL", IIF([COMPANY]="C2","COKATO", IIF([COMPANY]

[quoted text clipped - 5 lines]

=IIf([COMPANY]="C1","WDNVL") Or IIF([COMPANY]="C2","COKATO") _
Or IIF([COMPANY]="C3","NOVI")

 

[Ray]

I have a similar problem with:
IIf(IsNull([RegionCode]),[Region],IIf([RegionCode]="Northern Teams",
IN("NE","NW","YH"),[RegionCode]))
as a criteria. The error message is that this expression is too complex,
which is a problem as I'd like to do more nesting. Is there another way to
have multiple IIf criteria?

Thanks

Ray

 

[Ray]

Upon further testing, the problem is not the nested IIfs, but just:
IIf([RegionCode]="Northern Teams", IN("NE","NW","YH"),[RegionCode])
This works fine with the True expression as just "NW", but not with the
whole In group. I'm still looking for a solution though, if anyone has any
suggestions.

I have a similar problem with:
IIf(IsNull([RegionCode]),[Region],IIf([RegionCode]="Northern Teams",
IN("NE","NW","YH"),[RegionCode]))
as a criteria. The error message is that this expression is too complex,
which is a problem as I'd like to do more nesting. Is there another way to
have multiple IIf criteria?

Thanks

Ray

You did not say what the error was.

How can anyone guess as to what to do or try without knowing what the error
statement was?

 

[David F Cox]

I do not see what you are looking for "IN". e,g, [region] IN ("NW",.....

Upon further testing, the problem is not the nested IIfs, but just:
IIf([RegionCode]="Northern Teams", IN("NE","NW","YH"),[RegionCode])
This works fine with the True expression as just "NW", but not with the
whole In group. I'm still looking for a solution though, if anyone has any
suggestions.

I have a similar problem with:
IIf(IsNull([RegionCode]),[Region],IIf([RegionCode]="Northern Teams",
IN("NE","NW","YH"),[RegionCode]))
as a criteria. The error message is that this expression is too complex,
which is a problem as I'd like to do more nesting. Is there another way
to
have multiple IIf criteria?

Thanks

Ray

You did not say what the error was.

How can anyone guess as to what to do or try without knowing what the
error
statement was?

 

[Ray]

I want it to select records with "NE" or "NW" or "YH" in the [region] field
if the "Northern Teams" option is selected. I had assumed it would look in
this field as I was entering the criteria in design view. Now with:
IIf(IsNull([RegionCode]),[Region],IIf([RegionCode]="Northern Teams",[Region]
IN ("NE","NW","YH"),[RegionCode]))
I no longer get any error message, but I also don't get any results when I
select "Northern Teams" in the [RegionCode] combo box. I think I'm looking at
it too hard right now, time for lunch!

Thanks

I do not see what you are looking for "IN". e,g, [region] IN ("NW",.....

Upon further testing, the problem is not the nested IIfs, but just:
IIf([RegionCode]="Northern Teams", IN("NE","NW","YH"),[RegionCode])
This works fine with the True expression as just "NW", but not with the
whole In group. I'm still looking for a solution though, if anyone has any
suggestions.

I have a similar problem with:
IIf(IsNull([RegionCode]),[Region],IIf([RegionCode]="Northern Teams",
IN("NE","NW","YH"),[RegionCode]))
as a criteria. The error message is that this expression is too complex,
which is a problem as I'd like to do more nesting. Is there another way
to
have multiple IIf criteria?

Thanks

Ray

:

You did not say what the error was.

How can anyone guess as to what to do or try without knowing what the
error
statement was?

 

[David F Cox]

Apologies, I was answering in a hurry. I think you are intending
IIF([region] IN ("NW", etc

I want it to select records with "NE" or "NW" or "YH" in the [region] field
if the "Northern Teams" option is selected. I had assumed it would look in
this field as I was entering the criteria in design view. Now with:
IIf(IsNull([RegionCode]),[Region],IIf([RegionCode]="Northern
Teams",[Region]
IN ("NE","NW","YH"),[RegionCode]))
I no longer get any error message, but I also don't get any results when I
select "Northern Teams" in the [RegionCode] combo box. I think I'm looking
at
it too hard right now, time for lunch!

Thanks

I do not see what you are looking for "IN". e,g, [region] IN
("NW",.....

Upon further testing, the problem is not the nested IIfs, but just:
IIf([RegionCode]="Northern Teams", IN("NE","NW","YH"),[RegionCode])
This works fine with the True expression as just "NW", but not with the
whole In group. I'm still looking for a solution though, if anyone has
any
suggestions.

:

I have a similar problem with:
IIf(IsNull([RegionCode]),[Region],IIf([RegionCode]="Northern Teams",
IN("NE","NW","YH"),[RegionCode]))
as a criteria. The error message is that this expression is too
complex,
which is a problem as I'd like to do more nesting. Is there another
way
to
have multiple IIf criteria?

Thanks

Ray

:

You did not say what the error was.

How can anyone guess as to what to do or try without knowing what
the
error
statement was?

 

[KARL DEWEY]

If you have a lot of IIF's then one way is to create a cross reference table
with the tests and answers and then join it in your query.

C1 WDNVL
C2 COKATO
C3 NOVI

Upon further testing, the problem is not the nested IIfs, but just:
IIf([RegionCode]="Northern Teams", IN("NE","NW","YH"),[RegionCode])
This works fine with the True expression as just "NW", but not with the
whole In group. I'm still looking for a solution though, if anyone has any
suggestions.

I have a similar problem with:
IIf(IsNull([RegionCode]),[Region],IIf([RegionCode]="Northern Teams",
IN("NE","NW","YH"),[RegionCode]))
as a criteria. The error message is that this expression is too complex,
which is a problem as I'd like to do more nesting. Is there another way to
have multiple IIf criteria?

Thanks

Ray

You did not say what the error was.

How can anyone guess as to what to do or try without knowing what the error
statement was?