Solved

Access query via VBA problem with quotes

Posted on 2013-01-03
17
452 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!
0
Comment
Question by:etech0
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 39

Expert Comment

by:als315
ID: 38740836
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.
0
 
LVL 10

Author Comment

by:etech0
ID: 38740871
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.
0
 
LVL 39

Expert Comment

by:als315
ID: 38740904
Place result here.
0
 
LVL 10

Author Comment

by:etech0
ID: 38740913
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

0
 
LVL 39

Expert Comment

by:als315
ID: 38740978
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

0
 
LVL 57
ID: 38740980
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.
0
 
LVL 57
ID: 38740992
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));"
0
 
LVL 10

Author Comment

by:etech0
ID: 38741004
@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)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 10

Author Comment

by:etech0
ID: 38741011
@Jim: your code gives the same error (and the whole block of code turns red)
0
 
LVL 10

Author Comment

by:etech0
ID: 38741025
@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

0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 50 total points
ID: 38741065
Break it down as Anthony said:

sql =  "INSERT INTO TPLPaymentDetailT ( PaymentID, PaymentType, Amount, statementNo ) " 
sql = sql &  "SELECT " & co2id & " AS PaymentID, 'Payment Based on Audit' AS PaymentType, "
sql = sql & " IIf([partiallypaid]=False,[AmountPaid],[amountpaid]-" & DLookUp('amount','tplpaymentdetailt','statementno=' & [statementno] & "' AND type = 'Pay Based on Percentage'''))
sql = sql & " AS Amount, TPLTotalAmountPaidPerCompanyQ.promptstatement AS statementNo "
sql = sql & "FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompanyQ ON TPLStatementT.StatementNo = "
sql = sql & "TPLTotalAmountPaidPerCompanyQ.PromptStatement"
sql = sql &  "WHERE (((TPLTotalAmountPaidPerCompanyQ.Company)=2));"

Think I got it that time, but if not, you get the idea of what Anthony suggested.

Jim.
0
 
LVL 39

Accepted Solution

by:
als315 earned 450 total points
ID: 38741094
Test this:
SQL = "INSERT INTO TPLPaymentDetailT ( PaymentID, PaymentType, Amount, StatementNo ) "
SQL = SQL & "SELECT " & co2id & " AS PaymentID, 'Payment Based on Audit' AS PaymentType, IIf([partiallypaid]=False, "
SQL = SQL & "[AmountPaid],[amountpaid]- DLookUp(""amount"",""tplpaymentdetailt"",""statementno="" & [statementno] & "
SQL = SQL & """ AND type = 'Pay Based on Percentage'"")) AS Amount, TPLTotalAmountPaidPerCompanyQ.promptstatement AS StatementNo "
SQL = SQL & "FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompanyQ ON TPLStatementT.StatementNo = "
SQL = SQL & "TPLTotalAmountPaidPerCompanyQ.PromptStatement "
SQL = SQL & "WHERE (((TPLTotalAmountPaidPerCompanyQ.Company)=2));"

Open in new window

Place result into Query designer in SQL mode and check
0
 
LVL 10

Author Closing Comment

by:etech0
ID: 38741196
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38741266
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.
0
 
LVL 10

Author Comment

by:etech0
ID: 38741274
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38741312
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.
0
 
LVL 10

Author Comment

by:etech0
ID: 38741319
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now