complex query error

[geebee]

When I try to save the following query, I get an "expression too complex in
query expression" error:

SELECT M.[Loan Acct #], M.PopEnterDt, IIf(MP.[chg off date] Is Not Null And
MP.[loan status]=5,"CHARGEOFF",IIf(MP.[Inventory Status] In
("IN","SO"),"REPO",IIf(MP.[Paid Off?]=1,"PAIDOFF", IIf(MP.[chg off date] Is
Not Null And MP.[loan status]=5 And MP.[Active Inactive Flag] =
"A","BK_CHGOFF", IIf(MP.[Inventory Status] In ("IN","SO") And MP.[Active
Inactive Flag] = "A","BK-REPO",IIf(MP.[Paid Off?]=1 And MP.[Active Inactive
Flag] = "A","BK-PAYOFF", IIf(MP.[Loan Status]=0 And MP.[Days
Delinquent]<=0,"BK-CURR", IIf(MP.[Loan Status]=0 And MP.[Days Delinquent]
Between 1 And 30 And MP.[Active Inactive Flag] = "A", "BK-PASTDUE_B1-30",
IIf(MP.[Loan Status]=0 And MP.[Days Delinquent]>30 And MP.[Active Inactive
Flag] = "A", "BK-PASTDUE_" & MP.[Del Group], IIf(MP.[Loan Status]=0 And
MP.[Days Delinquent]<=0,"CURRENT", IIf(MP.[Loan Status]=0 And MP.[Days
Delinquent] Between 1 And 30,"PASTDUE_B1-30", IIf(MP.[Loan Status]=0 And
MP.[Days Delinquent]>30,"PASTDUE_" & MP.[Del Group], IIf(MP.[Loan Status]=0
And MP.[Days Delinquent]<=0,"CURRENT", IIf(MP.[Loan Status]=0 And MP.[Days
Delinquent] Between 1 And 30,"PASTDUE_B1-30", IIf(MP.[Loan Status]=0 And
MP.[Days Delinquent]>30,"PASTDUE_" & MP.[Del Group],"OTHER"))))))))))))))))
AS WBStatus, M.Status, M.PrevStatus, MP.[Days Delinquent] AS Expr1, MP.[Paid
Off?] AS Expr2, MP.[Amended This Mo?] AS Expr3, MP.[Extended This Mo?] AS
Expr4, MP.[Active Inactive Flag] AS Expr5, MP.[Inventory Status] AS Expr6
FROM tbl_masterpop M, MasterPop MP;

does anyone know what's going on?

thanks in advance,
geebee

 

[Barry Gilbert]

Your huge nested IIF statement probably makes Jet throw up it's hands and say
"Oh, what's the use?"

I would suggest figuring out a different strategy to get these return
values, perhaps from a table or a function call.

Barry