Expression Error

[Deb Struble]

I have created an expression in my query that looks like this:

ModelName: IIf([strModelName]="T6 HSR","T6",IIf([strModelName]="T8
HSR","T8",IIf([strModelName]="M5","Misc",IIf([strModelName]="S6","Misc",IIf([strModelName]="ST48","Misc",IIf([strModelName]="FDR","Misc",IIf([strModelName]="M4(DW)","M4",IIf([strModelName]="MX8(N)","MX8",IIf([strModelName]="M8(C)","M8",IIf([strModelName]="M8(N)","M8",IIf([strModelName]="M6(C)","M6",IIf([strModelName]="M6(N)","M6",IIf([strModelName]="MX7(N)","MX7",[strModelName])))))))))))))

I need to add more If statements but I get the "Expression you have entered
is too complex" error when I add more statements. I could probably combine
a few of these statements using "And" and "Or" but I am not sure how to do
that. Any suggestions?
Thanks in advance for any help!

 

[Duane Hookom]

I would create a user defined function that would accept the strModelName as
an argument and return the ModelName value:


Public Function GetModelName(astrModName as String) as String
Select Case astrModName
Case "T6 HSR"
GetModelName ="T6"
Case "T8 HSR"
GetModelName ="T8"
Case "M5"
GetModelName ="Misc"
...you add more here...
Case Else
GetModelName = astrModName
End Select
End Function

Ideally values like this should come from and be maintained in a lookup
table.

 

[MGFoster]

I have created an expression in my query that looks like this:

ModelName: IIf([strModelName]="T6 HSR","T6",IIf([strModelName]="T8
HSR","T8",IIf([strModelName]="M5","Misc",IIf([strModelName]="S6","Misc",IIf([strModelName]="ST48","Misc",IIf([strModelName]="FDR","Misc",IIf([strModelName]="M4(DW)","M4",IIf([strModelName]="MX8(N)","MX8",IIf([strModelName]="M8(C)","M8",IIf([strModelName]="M8(N)","M8",IIf([strModelName]="M6(C)","M6",IIf([strModelName]="M6(N)","M6",IIf([strModelName]="MX7(N)","MX7",[strModelName])))))))))))))

I need to add more If statements but I get the "Expression you have entered
is too complex" error when I add more statements. I could probably combine
a few of these statements using "And" and "Or" but I am not sure how to do
that. Any suggestions?

IIf(strModelName In ("M5", "S6", "ST48"), "Misc")

Put more values in the IN (...) parentheses, as shown above, for the
"Misc" result.