I've got an access query (which contains a dlookup) that I'm trying to run via VBA, and it's giving me an error. I think it's a double/single quote problem, but I can't figure it out.
Here's the query:
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 " & _
On the fourth line, it gives me an error "Expected: End of Statement"
Can anyone help me fix this? Thanks!