Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL syntax error missing operator

Status
Not open for further replies.

end922

Technical User
Apr 17, 2007
59
US
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
 
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
 
no luck with that. same error message received
 
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
 
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
 

Try:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top