With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
Solved
Access query via VBA problem with quotes
Posted on 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:
Can anyone help me fix this? Thanks!
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));"
Can anyone help me fix this? Thanks!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
8
4
3- +1
17 Comments
Active today
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
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.
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.
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));"
Active today
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 = "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]"
Active today
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.
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.
Active today
sql = "INSERT INTO TPLPaymentDetailT ( PaymentID, PaymentType, Amount, StatementNo ) " & _
"SELECT " & co2id & " AS PaymentID, 'Payment Based on Audit' AS PaymentType, IIf([partiallypaid]=False,
"[AmountPaid],[amountpaid]
"' AND type = 'Pay Based on Percentage'')) & " AS Amount, TPLTotalAmountPaidPerCompa
"FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompa
"TPLTotalAmountPaidPerComp
"WHERE (((TPLTotalAmountPaidPerCo
"SELECT " & co2id & " AS PaymentID, 'Payment Based on Audit' AS PaymentType, IIf([partiallypaid]=False,
"[AmountPaid],[amountpaid]
"' AND type = 'Pay Based on Percentage'')) & " AS Amount, TPLTotalAmountPaidPerCompa
"FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompa
"TPLTotalAmountPaidPerComp
"WHERE (((TPLTotalAmountPaidPerCo
@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)
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)
@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));"
Active today
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,
sql = sql & " AS Amount, TPLTotalAmountPaidPerCompa
sql = sql & "FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompa
sql = sql & "TPLTotalAmountPaidPerComp
sql = sql & "WHERE (((TPLTotalAmountPaidPerCo
Think I got it that time, but if not, you get the idea of what Anthony suggested.
Jim.
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,
sql = sql & " AS Amount, TPLTotalAmountPaidPerCompa
sql = sql & "FROM TPLStatementT RIGHT JOIN TPLTotalAmountPaidPerCompa
sql = sql & "TPLTotalAmountPaidPerComp
sql = sql & "WHERE (((TPLTotalAmountPaidPerCo
Think I got it that time, but if not, you get the idea of what Anthony suggested.
Jim.
Active today
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));"
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.
Jim: thanks for your help as well.
Active 1 day ago
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.
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.
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.
Active 1 day ago
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.
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.
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.
Featured Post
Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page.
The problem with sub-reports and headings:
Add a dummy group to the sub report using the expression =1:
Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month6 days, 11 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.