query error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geebee - 08 May 2006 22:43 GMT

hi,

I have the following query for which I am getting a "syntax error in query
expression". But I can't figure out what is causing the 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,"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.[Active Inactive Flag]="A","BK", 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;

can anyone tell me what is wrong with this?

thanks in advance,
geebee

Reply to this Message

KARL DEWEY - 09 May 2006 00:01 GMT

I find you have nested 19 IIF statements but only closed 5.

> hi,
>
[quoted text clipped - 29 lines]
> thanks in advance,
> geebee

Reply to this Message

geebee - 09 May 2006 14:26 GMT

I am getting an "expression" too complex when I try to save the query. The
query is as follows:

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;

> I find you have nested 19 IIF statements but only closed 5.
>
[quoted text clipped - 31 lines]
> > thanks in advance,
> > geebee

Reply to this Message

KARL DEWEY - 09 May 2006 17:39 GMT

Try reducing the complexity - Build a little - Test a little.

> I am getting an "expression" too complex when I try to save the query. The
> query is as follows:
[quoted text clipped - 56 lines]
> > > thanks in advance,
> > > geebee

Reply to this Message