Iif statments

[Guest]

Hello

I have built an Iif statement in a query that will not run because it says
it is to complex. If I limit the amount of Iif''s the query runs fine. I need
it to run all 15 scenarios. Here is my Iif statement does anyone know how I
can make this work?

Expr1:
IIf([AvgMatPts]=15,"CH",IIf([AvgMatPts]=14,"CM",IIf([AvgMatPts]=13,"CL",IIf([AvgMatPts]=12,"OH",IIf([AvgMatPts]=11,"OM",IIf([AvgMatPts]=10,"OL",IIf([AvgMatPts]=9,"MH",IIf([AvgMatPts]=8,"MM",IIf([AvgMatPts]=7,"ML",IIf([AvgMatPts]=6,"RH",IIf([AvgMatPts]=5,"RM",IIf([AvgMatPts]=4,"RL",IIf([AvgMatPts]=3,"UH",IIf([AvgMatPts]=2,"UM",IIf([AvgMatPts]=1,"UL",[AvgMatPts])))))))))))))))

 

[[MVP] S.Clark]

That is very complex. Can you create tables to perform joins, instead of
IIF() for everything?

 

[Guest]

I am unfamiliar with a table for joins. Is this creating a lookup field in a
sense.

Thanks

That is very complex. Can you create tables to perform joins, instead of
IIF() for everything?


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Hello

I have built an Iif statement in a query that will not run because it says
it is to complex. If I limit the amount of Iif''s the query runs fine. I
need
it to run all 15 scenarios. Here is my Iif statement does anyone know how
I
can make this work?

Expr1:
IIf([AvgMatPts]=15,"CH",IIf([AvgMatPts]=14,"CM",IIf([AvgMatPts]=13,"CL",IIf([AvgMatPts]=12,"OH",IIf([AvgMatPts]=11,"OM",IIf([AvgMatPts]=10,"OL",IIf([AvgMatPts]=9,"MH",IIf([AvgMatPts]=8,"MM",IIf([AvgMatPts]=7,"ML",IIf([AvgMatPts]=6,"RH",IIf([AvgMatPts]=5,"RM",IIf([AvgMatPts]=4,"RL",IIf([AvgMatPts]=3,"UH",IIf([AvgMatPts]=2,"UM",IIf([AvgMatPts]=1,"UL",[AvgMatPts])))))))))))))))

 

[fredg]

Hello

I have built an Iif statement in a query that will not run because it says
it is to complex. If I limit the amount of Iif''s the query runs fine. I need
it to run all 15 scenarios. Here is my Iif statement does anyone know how I
can make this work?

Expr1:

IIf([AvgMatPts]=15,"CH",IIf([AvgMatPts]=14,"CM",IIf([AvgMatPts]=13,
"CL",IIf([AvgMatPts]=12,"OH",IIf([AvgMatPts]=11,"OM",IIf([AvgMatPts]=10,
"OL",IIf([AvgMatPts]=9,"MH",IIf([AvgMatPts]=8,"MM",IIf([AvgMatPts]=7,
"ML",IIf([AvgMatPts]=6,"RH",IIf([AvgMatPts]=5,"RM",IIf([AvgMatPts]=4,
"RL",IIf([AvgMatPts]=3,"UH",IIf([AvgMatPts]=2,"UM",IIf([AvgMatPts]=1,
"UL",[AvgMatPts])))))))))))))))

Gee! My eyes are killin' me.

Simply create a public function in a module using Select Case (or an
If..Then... Else statement,
i.e.
Function GetValue(ValueIn) as string
Select Case ValueIn
Case is = 1
GetValue = "UL"
Case is = 2
GetValue = "UM"
Etc...
End Select
End Function


and call the function from the query:
Expr1:GetValue([FieldName])

A lookup table is also good as it makes it easier to add additional
items in the future without having to re-write the code.

 

[giorgio rancati]

Hi,

the best way is create a table but you can also use a Choose function
----
Expr1:Choose([AvgMatPts],"UL","UM","UH","RL","RM","RH","ML","MM","MH","OL","
OM","OH","CL","CM","CH")
----
it isn't complex

bye
--
Giorgio Rancati
[Office Access MVP]

Hello

I have built an Iif statement in a query that will not run because it says
it is to complex. If I limit the amount of Iif''s the query runs fine. I need
it to run all 15 scenarios. Here is my Iif statement does anyone know how I
can make this work?

Expr1:

IIf([AvgMatPts]=15,"CH",IIf([AvgMatPts]=14,"CM",IIf([AvgMatPts]=13,"CL",IIf(
[AvgMatPts]=12,"OH",IIf([AvgMatPts]=11,"OM",IIf([AvgMatPts]=10,"OL",IIf([Avg
MatPts]=9,"MH",IIf([AvgMatPts]=8,"MM",IIf([AvgMatPts]=7,"ML",IIf([AvgMatPts]
=6,"RH",IIf([AvgMatPts]=5,"RM",IIf([AvgMatPts]=4,"RL",IIf([AvgMatPts]=3,"UH"
,IIf([AvgMatPts]=2,"UM",IIf([AvgMatPts]=1,"UL",[AvgMatPts])))))))))))))))