Expression Error

  • Thread starter Thread starter Deb Struble
  • Start date Start date
D

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!
 
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.
 
Deb said:
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?
Click to expand...

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

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