CurrentDb.Execute "INSERT INTO TPLPaymentDetailT ( PaymentID, PaymentType, Amount, StatementNo ) " & _
"SELECT " & co2id & " AS PaymentID, 'Payment Based on Audit' AS PaymentType, IIf([partiallypaid]=False, " & _
"[AmountPaid],[amountpaid]- DLookUp('amount','tplpaymentdetailt','statementno=' & [statementno] & _
"' AND type = 'Pay Based on Percentage'')) AS Amount, TPLTotalAmountPaidPerCompanyQ.promptstatement AS StatementNo " & _
"FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompanyQ ON TPLStatementT.StatementNo = " & _
"TPLTotalAmountPaidPerCompanyQ.PromptStatement " & _
"WHERE (((TPLTotalAmountPaidPerCompanyQ.Company)=2));"
On the fourth line, it gives me an error "Expected: End of Statement"sql = "INSERT INTO TPLPaymentDetailT ( PaymentID, PaymentType, Amount, StatementNo ) " & _
"SELECT " & co2id & " AS PaymentID, 'Payment Based on Audit' AS PaymentType, IIf([partiallypaid]=False, " & _
"[AmountPaid],[amountpaid]- DLookUp('amount','tplpaymentdetailt','statementno=' & [statementno] & _
"' AND type = 'Pay Based on Percentage'')) AS Amount, TPLTotalAmountPaidPerCompanyQ.promptstatement AS StatementNo " & _
"FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompanyQ ON TPLStatementT.StatementNo = " & _
"TPLTotalAmountPaidPerCompanyQ.PromptStatement " & _
"WHERE (((TPLTotalAmountPaidPerCompanyQ.Company)=2));"
SQL = SQL & "- DLookUp(" & Chr(34) & "amount" & Chr(34) & ",""tplpaymentdetailt"",""statementno="" & [statementno]"
sql = "INSERT INTO TPLPaymentDetailT ( PaymentID, PaymentType, Amount, StatementNo ) " & _
"SELECT " & co2id & " AS PaymentID, 'Payment Based on Audit' AS PaymentType, IIf([partiallypaid]=False, " & _
"[AmountPaid],[amountpaid]- & DLookUp(" & chr(34) & "amount" & chr(34) & "," & chr(34) & "tplpaymentdetailt" & chr(34) & "," & chr(34) & "statementno=' & [statementno] & _
"' AND type = 'Pay Based on Percentage'" & chr(34) & ")) & " AS Amount, TPLTotalAmountPaidPerCompanyQ.promptstatement AS StatementNo " & _
"FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompanyQ ON TPLStatementT.StatementNo = " & _
"TPLTotalAmountPaidPerCompanyQ.PromptStatement " & _
"WHERE (((TPLTotalAmountPaidPerCompanyQ.Company)=2));"
Open in new window
You will see errors.