SQL syntax error missing operator

[end922]

Greetings, trying to add this sql statment to VBA. Keep getting Syntax error (missing operator)starting at the IIF part of the statement. Not that familiar with this.
Thanks
E

SSQL = "INSERT INTO DataTrac ( MITLoan, EscrowBal, Srv_Rel_Date, DateRec, GMACLoan, InvestorLoan," & _
"Funded, PandI, TandI, MI, FirstPmtDate, BFirst, BLast, Purchased, DueToInvestor, TradeType, CommitmentNum," & _
"Branch, InvestorName, BegBalance )SELECT Ztbl_Impounds.LoanNum, Ztbl_Impounds.EscrowBal, Ztbl_Impounds.SrvRelDate," & _
"Ztbl_Impounds.DateRec, dbo_GEN.srv_loan_no, dbo_GEN.inv_loan_no, dbo_GEN.funded, dbo_GEN.p_and_i," & _
"dbo_GEN.imp_pmt, dbo_GEN.pmi_mmi_amt, dbo_GEN.first_pmt, Trim([borrow_fn]) AS BFirstN, Trim([borrow_ln]) AS BLastN," & _
"dbo_ACT.purchased, dbo_ACT.due_investor, Iif([Commit_No] Like '1*' Or [Commit_No] Like '2*' Or [Commit_No] Like '3*'," & _
"Or [Commit_No] Like '4*' Or [Commit_No] Like '5*' Or [Commit_No] Like '6*' Or [Commit_No] Like '7*'," & _
"Or [Commit_No] Like '8*' Or [Commit_No] Like '9*','Flow',IIf([Trade_Due] Is Not Null,'Bulk')) AS TradeType," & _
"dbo_Mkt.commit_no, dbo_Branches.short_name, dbo_Investor.investor, tbl_GMAC_ServicingData.[Escrow Bal] FROM (((((Ztbl_Impounds LEFT JOIN dbo_GEN ON Ztbl_Impounds.file_id = dbo_GEN.file_id) LEFT JOIN dbo_ACT ON Ztbl_Impounds.file_id = dbo_ACT.file_id) LEFT JOIN dbo_Mkt ON Ztbl_Impounds.file_id = dbo_Mkt.file_id) LEFT JOIN tbl_GMAC_ServicingData ON Ztbl_Impounds.LoanNum = tbl_GMAC_ServicingData.MIT_LoanNum) LEFT JOIN dbo_Branches ON Ztbl_Impounds.site_id = dbo_Branches.site_id) LEFT JOIN dbo_Investor ON Ztbl_Impounds.investor_id = dbo_Investor.investor_id;"
DoCmd.RunSQL SSQL




End Sub

 

[xlbo]

Iif([Commit_No] Like '1*' Or [Commit_No] Like '2*' Or [Commit_No] Like '3*', Or [Commit_No] Like '4*' Or [Commit_No] Like '5*' Or [Commit_No] Like '6*' Or [Commit_No] Like '7*',Or [Commit_No] Like '8*' Or [Commit_No] Like '9*','Flow',IIf([Trade_Due] Is Not Null,'Bulk',NEEDS FALSE PART HERE)) AS TradeType

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[end922]

no luck with that. same error message received

 

[xlbo]

Did you put a false part in or just copy what I put into your SQL string ?

also, is it a SQL syntax error or a VBA syntax error ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GhostWolf]

The first thing I notice: there's a space missing at "BegBalance )SELECT".

Then, as xlbo noted, there's no False result for the second IIF.

And how about a simplification:
IIF(left([Commit_No],1) between '1' and '9', 'Flow',IIF([Trade_Due] is null, 'Bulk','Unknown Type')) as TradeType

hth

 

[Andrzejek]

Try:

SSQL = "INSERT INTO DataTrac ...
[blue]Debug.Print SSQL[/blue]
DoCmd.RunSQL SSQL

Copy your SQL from Immidiete Window and paste it into some tool to run your statement against your DB.

Many times just by looking close at the statement you can see what's going on and figure it out.

Have fun.

---- Andy