query error

G

geebee

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
 
G

geebee

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;



KARL DEWEY said:
I find you have nested 19 IIF statements but only closed 5.

geebee said:
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
Click to expand...
Click to expand...
 
K

KARL DEWEY

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

geebee said:
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;



KARL DEWEY said:
I find you have nested 19 IIF statements but only closed 5.

geebee said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

complex query error 1
change to UPDATE query 3
delete query 1
query speed 5
runtime error 1
join type not supported 5
Query Error 2
runtime error 3001 invalid argument error message 2

Top