runtime error

G

Guest

I have the following query:

Dim strsqlappend As String

strsqlappend = "SELECT [Loan Acct #]," _
& "((IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And [Status]='CURR'
And ([Amended this Mo?]=1 Or [Extended This Mo?]=1) And [Paid Off?]=0 And
[Active Inactive Flag] In ('I','A'),'BK_CURR_AMD/DFR',IIf([PrevStatus]<>'NEW'
And [PrevStatus] Like 'PD*' And [Status]='CURR' And ([Amended this Mo?]=1 Or
[Extended This Mo?]=1) And [Paid Off?]=0 And [Active Inactive Flag =
'A','BK_CURR_AMD/DFR',IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And
[Status]='CURR' And ([Amended this Mo?]=0 Or [Extended This Mo?]=0) And [Paid
Off?]=0 And [Active Inactive Flag] = 'A','BK_CURR_PD',
IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'REPO' And [Status]='REPO' And
[INVENTORY STATUS] In ('IN','SO') And [Active Inactive Flag] =
'A','BK_REPO',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'PAYOFF' And
[Status]='PAYOFF' And [Active Inactive Flag] = 'A','BK_PAYOFF',
IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'CHGOFF' And [Status]='CHGOFF' And
[Active Inactive Flag] = 'A','BK_CHGOFF'," _
& "IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And [Status]='CURR'
And ([Amended this Mo?]=1 Or [Extended This Mo?]=1) And [Paid Off?]=0 And
[Active Inactive Flag] In ('I','A'),'CURR_BKAMD/DFR', IIf([PrevStatus]<>'NEW'
And [PrevStatus] Like 'PD*' And [Status]='CURR' And ([Amended this Mo?]=1 Or
[Extended This Mo?]=1) And [Paid
Off?]=0,'CURR_AMD/DFR',IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*'
And [Status]='CURR' And ([Amended this Mo?]=0 Or [Extended This Mo?]=0) And
[Paid Off?]=0,'CURR_PD',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'REPO' And
[Status]='REPO' And [INVENTORY STATUS] In
('IN','SO'),'REPO',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'PAYOFF' And
[Status]='PAYOFF','PAYOFF',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'CHGOFF'
And [Status]='CHGOFF','CHGOFF'" _
& ",Null))))))))))) " _
& "AS ResolvedStatus," _
& "IIf(Weekday(Date) = 2, Date - 2, Date - 1) As RslvdDate,M.PopEnterDt,
M.PrevStatus, M.[Buying Center #], M.[Division Name]," _
& "M.[Region Name] , M.[Group Name], M.[Collection Center Name],
M.[Distribution Channel], M.[New Institution Name] , M.[Origination Channel],
M.[Sub Channel], M.[Short Name],M.[Loan Status] , M.[Contract Date], M.[Mat
date], M.[Next Pmt Due Date],M.[Payoff Date] , M.[Paid Off?], M.[Nbr of Reg
Pmts Made], M.[# Reg Pmts Remaining],M.[Pmt Scd Monthly Pmt Amt] , M.[Last
Pmt Date], M.[Last Pmt Amt], M.[Amended?],M.[Amended This Mo?] , M.[DEL
Group], M.[Days Delinquent], M.[Tot Past Due Pmts $], " _
& "M.[Principal Balance] , M.[Val Reserve Dollar Amt], M.NETBAL,
M.[Ext?],M.[Extended This Mo?] , M.[Payoff Dollar Amt], M.[Flat Void Mth of
Orig], M.[Active BK Flag Date],M.[Active BK Flag] , M.[INVENTORY STATUS],
M.[Charge Off Principal], M.[Chg Off Type],M.[CHG OFF Date] , M.[Cure Letter
Date], M.[Skip Active Date], M.[Skip Completion Date],M.[Skip Completion Sts]
, M.[Redeemed Date], M.[Entered Date], M.[Repo Ordered Date],M.[Active
Inactive Flag]" _
& "FROM Tbl_MasterPop M" _
& "WHERE (((M.[Loan Acct #]) Is Not Null) AND " _
& "((IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And [Status]='CURR'
And ([Amended this Mo?]=1 Or [Extended This Mo?]=1) And [Paid Off?]=0 And
[Active Inactive Flag] In ('I','A'),'BK_CURR_AMD/DFR',IIf([PrevStatus]<>'NEW'
And [PrevStatus] Like 'PD*' And [Status]='CURR' And ([Amended this Mo?]=1 Or
[Extended This Mo?]=1) And [Paid Off?]=0 And [Active Inactive Flag =
'A','BK_CURR_AMD/DFR',IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And
[Status]='CURR' And ([Amended this Mo?]=0 Or [Extended This Mo?]=0) And [Paid
Off?]=0 And [Active Inactive Flag] = 'A','BK_CURR_PD',
IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'REPO' And [Status]='REPO' And
[INVENTORY STATUS] In ('IN','SO') And [Active Inactive Flag] =
'A','BK_REPO',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'PAYOFF' And
[Status]='PAYOFF' And [Active Inactive Flag] = 'A','BK_PAYOFF',
IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'CHGOFF' And [Status]='CHGOFF' And
[Active Inactive Flag] = 'A','BK_CHGOFF'," _
& "IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And [Status]='CURR'
And ([Amended this Mo?]=1 Or [Extended This Mo?]=1) And [Paid Off?]=0 And
[Active Inactive Flag] In ('I','A'),'CURR_BKAMD/DFR', IIf([PrevStatus]<>'NEW'
And [PrevStatus] Like 'PD*' And [Status]='CURR' And ([Amended this Mo?]=1 Or
[Extended This Mo?]=1) And [Paid
Off?]=0,'CURR_AMD/DFR',IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*'
And [Status]='CURR' And ([Amended this Mo?]=0 Or [Extended This Mo?]=0) And
[Paid Off?]=0,'CURR_PD',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'REPO' And
[Status]='REPO' And [INVENTORY STATUS] In
('IN','SO'),'REPO',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'PAYOFF' And
[Status]='PAYOFF','PAYOFF',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'CHGOFF'
And [Status]='CHGOFF','CHGOFF'" _
& ",Null)))))))))))) Is Not Null));"

DoCmd.RunSQL strsqlappend

however, when I try to run it, I get a runtime error 3075, syntax error
(missing operator) in query expression. what's missing?

thanks in advance,
geebee
 
D

Duane Hookom

Open the debug window and enter:
? strSQLappend
Copy the result into a blank query sql view. Remove stuff until it works.

--
Duane Hookom
MS Access MVP

geebee said:
I have the following query:

Dim strsqlappend As String

strsqlappend = "SELECT [Loan Acct #]," _
& "((IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And
[Status]='CURR'
And ([Amended this Mo?]=1 Or [Extended This Mo?]=1) And [Paid Off?]=0 And
[Active Inactive Flag] In
('I','A'),'BK_CURR_AMD/DFR',IIf([PrevStatus]<>'NEW'
And [PrevStatus] Like 'PD*' And [Status]='CURR' And ([Amended this Mo?]=1
Or
[Extended This Mo?]=1) And [Paid Off?]=0 And [Active Inactive Flag =
'A','BK_CURR_AMD/DFR',IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*'
And
[Status]='CURR' And ([Amended this Mo?]=0 Or [Extended This Mo?]=0) And
[Paid
Off?]=0 And [Active Inactive Flag] = 'A','BK_CURR_PD',
IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'REPO' And [Status]='REPO' And
[INVENTORY STATUS] In ('IN','SO') And [Active Inactive Flag] =
'A','BK_REPO',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'PAYOFF' And
[Status]='PAYOFF' And [Active Inactive Flag] = 'A','BK_PAYOFF',
IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'CHGOFF' And [Status]='CHGOFF'
And
[Active Inactive Flag] = 'A','BK_CHGOFF'," _
& "IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And [Status]='CURR'
And ([Amended this Mo?]=1 Or [Extended This Mo?]=1) And [Paid Off?]=0 And
[Active Inactive Flag] In ('I','A'),'CURR_BKAMD/DFR',
IIf([PrevStatus]<>'NEW'
And [PrevStatus] Like 'PD*' And [Status]='CURR' And ([Amended this Mo?]=1
Or
[Extended This Mo?]=1) And [Paid
Off?]=0,'CURR_AMD/DFR',IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*'
And [Status]='CURR' And ([Amended this Mo?]=0 Or [Extended This Mo?]=0)
And
[Paid Off?]=0,'CURR_PD',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'REPO'
And
[Status]='REPO' And [INVENTORY STATUS] In
('IN','SO'),'REPO',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'PAYOFF' And
[Status]='PAYOFF','PAYOFF',IIf([PrevStatus]<>'NEW' And
[PrevStatus]<>'CHGOFF'
And [Status]='CHGOFF','CHGOFF'" _
& ",Null))))))))))) " _
& "AS ResolvedStatus," _
& "IIf(Weekday(Date) = 2, Date - 2, Date - 1) As RslvdDate,M.PopEnterDt,
M.PrevStatus, M.[Buying Center #], M.[Division Name]," _
& "M.[Region Name] , M.[Group Name], M.[Collection Center Name],
M.[Distribution Channel], M.[New Institution Name] , M.[Origination
Channel],
M.[Sub Channel], M.[Short Name],M.[Loan Status] , M.[Contract Date],
M.[Mat
date], M.[Next Pmt Due Date],M.[Payoff Date] , M.[Paid Off?], M.[Nbr of
Reg
Pmts Made], M.[# Reg Pmts Remaining],M.[Pmt Scd Monthly Pmt Amt] , M.[Last
Pmt Date], M.[Last Pmt Amt], M.[Amended?],M.[Amended This Mo?] , M.[DEL
Group], M.[Days Delinquent], M.[Tot Past Due Pmts $], " _
& "M.[Principal Balance] , M.[Val Reserve Dollar Amt], M.NETBAL,
M.[Ext?],M.[Extended This Mo?] , M.[Payoff Dollar Amt], M.[Flat Void Mth
of
Orig], M.[Active BK Flag Date],M.[Active BK Flag] , M.[INVENTORY STATUS],
M.[Charge Off Principal], M.[Chg Off Type],M.[CHG OFF Date] , M.[Cure
Letter
Date], M.[Skip Active Date], M.[Skip Completion Date],M.[Skip Completion
Sts]
, M.[Redeemed Date], M.[Entered Date], M.[Repo Ordered Date],M.[Active
Inactive Flag]" _
& "FROM Tbl_MasterPop M" _
& "WHERE (((M.[Loan Acct #]) Is Not Null) AND " _
& "((IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And
[Status]='CURR'
And ([Amended this Mo?]=1 Or [Extended This Mo?]=1) And [Paid Off?]=0 And
[Active Inactive Flag] In
('I','A'),'BK_CURR_AMD/DFR',IIf([PrevStatus]<>'NEW'
And [PrevStatus] Like 'PD*' And [Status]='CURR' And ([Amended this Mo?]=1
Or
[Extended This Mo?]=1) And [Paid Off?]=0 And [Active Inactive Flag =
'A','BK_CURR_AMD/DFR',IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*'
And
[Status]='CURR' And ([Amended this Mo?]=0 Or [Extended This Mo?]=0) And
[Paid
Off?]=0 And [Active Inactive Flag] = 'A','BK_CURR_PD',
IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'REPO' And [Status]='REPO' And
[INVENTORY STATUS] In ('IN','SO') And [Active Inactive Flag] =
'A','BK_REPO',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'PAYOFF' And
[Status]='PAYOFF' And [Active Inactive Flag] = 'A','BK_PAYOFF',
IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'CHGOFF' And [Status]='CHGOFF'
And
[Active Inactive Flag] = 'A','BK_CHGOFF'," _
& "IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*' And [Status]='CURR'
And ([Amended this Mo?]=1 Or [Extended This Mo?]=1) And [Paid Off?]=0 And
[Active Inactive Flag] In ('I','A'),'CURR_BKAMD/DFR',
IIf([PrevStatus]<>'NEW'
And [PrevStatus] Like 'PD*' And [Status]='CURR' And ([Amended this Mo?]=1
Or
[Extended This Mo?]=1) And [Paid
Off?]=0,'CURR_AMD/DFR',IIf([PrevStatus]<>'NEW' And [PrevStatus] Like 'PD*'
And [Status]='CURR' And ([Amended this Mo?]=0 Or [Extended This Mo?]=0)
And
[Paid Off?]=0,'CURR_PD',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'REPO'
And
[Status]='REPO' And [INVENTORY STATUS] In
('IN','SO'),'REPO',IIf([PrevStatus]<>'NEW' And [PrevStatus]<>'PAYOFF' And
[Status]='PAYOFF','PAYOFF',IIf([PrevStatus]<>'NEW' And
[PrevStatus]<>'CHGOFF'
And [Status]='CHGOFF','CHGOFF'" _
& ",Null)))))))))))) Is Not Null));"

DoCmd.RunSQL strsqlappend

however, when I try to run it, I get a runtime error 3075, syntax error
(missing operator) in query expression. what's missing?

thanks in advance,
geebee
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

delete query 1
join type not supported 5
query speed 5
Query Error 2
query error 3
change to UPDATE query 3
combine 2 access queries 3
complex query error 1

Top