We help IT Professionals succeed at work.

Access query via VBA problem with quotes

etech0
etech0 asked
on
508 Views
Last Modified: 2013-01-03
Hi!

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 " & _
    "WHERE (((TPLTotalAmountPaidPerCompanyQ.Company)=2));"

Open in new window

On the fourth line, it gives me an error "Expected: End of Statement"

Can anyone help me fix this? Thanks!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
It is not so simple to debug so complex query. Assign query to variable. In this case you can debug it without problems.
SQL = "INSERT INTO TPLPaymentDetailT ( PaymentID, PaymentType, Amount, StatementNo ) "
SQL = SQL & "SELECT " & co2id & " AS PaymentID, 'Payment Based on Audit' AS PaymentType, IIf([partiallypaid]=False, "
.....
....

debug.print SQL

Open in new window

You will see errors.

Author

Commented:
Okay, I did that. Now the whole SQL= section turned red, and I can't debug it because the code won't run - syntax error.

Any ideas? My guess is that it may be the two single quotes on line 4, but I don't know how else to write that dlookup.
CERTIFIED EXPERT

Commented:
Place result here.

Author

Commented:
Can't do debug.print sql, because I can't get that subroutine running due to syntax error.

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));"

Open in new window

CERTIFIED EXPERT

Commented:
Divide string as I've suggested:
SQL = "first line"
SQL = SQL & "Second line"
You will be able to find line with error. Divide this line to small parts and find problem part.
If you need quote in string, you can use chr(34) or double "". For example:
SQL = SQL & "- DLookUp(" & Chr(34) & "amount" & Chr(34) & ",""tplpaymentdetailt"",""statementno="" & [statementno]"

Open in new window

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
First, rather then get the value of the dlookup in the statement, fetch the value first into a variable, then use the variable in the SQL statement.

That makes it easier.

Second, why are you using a Dlookup() in a SQL statement?  The domain functions represent SQL statements and are meant to be used in places where SQL is not.  Here you can.

 The problem with them in this context is that the query parser cannot optimize them and you will always get poor performance.

Jim.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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));"

Author

Commented:
@Jim:
I'm using DLookup in SQL, because I don't know how to do that particular join.

What I need: if a certain field (PartiallyPaid) is flagged, then join with another table and pick up a matching value with criteria with another field. No clue how to do that.

I can't fetch the variable and put it into the sql, because this sql inserts multiple rows, and the target value is different for each row.

@als315: I will try using Chr(34)

Author

Commented:
@Jim: your code gives the same error (and the whole block of code turns red)

Author

Commented:
@als315: replacing the quotes for the dlookup with Chr(34)  did not help. This is my new code:
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

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
alas315: That did it! I don't totally understand the double quotes and all, but if it works...
Jim: thanks for your help as well.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Just a thought, what is the relationship between tplStatementT and tplPaymentDetailT?  is this a 1-to-1, 1-to-0 or 1 (may not be a detail record for each statement), or 1-to-many?

If it is a 1-to-1 or 1-to-0 or 1, then you could use an outer join to connect those tables, but if it is 1-to-many relationship, you need to replace the DLOOKUP in your query with a DSUM(), since the DLOOKUP will only return the 1st value from that table, not all of the values.

Author

Commented:
1 to many. However, I'd only want the join when a certain field in tplStatementT is flagged. Is that possible? If it's not, I don't want to end up with all those extra rows from tplPaymentDetailT.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
You could do a join to a sub-query which would group by the join field and sum across the Amount field in that table.  This would be quicker than using DLOOKUP or DSUM.  But you definately should consider using the DSUM() function rather than DLOOKUP(), otherwise, you will only subtract a single value, rather than the sum of all payments.

Author

Commented:
There will only ever be one record in the tplPaymentDetailT with a type of "Pay Based on Percentage" for any particular statement record. That's why I used Dlookup.

I could try a subquery, but for now I think I'll leave it as it is. After all, it works, and it's not too slow because the query updates an average of 1-3 records. But I'll keep it in mind.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.