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
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