VBA SQL help

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

mattc66 via AccessMonster.com

I am trying to run the below SQL statement in code. VBA doesn't like it. I am
trying to wrap the text as well. I have searched help on wraping of text and
I find the below is one method. I am not sure.

Could anyone help? Thanks Matt

DoCmd.RunSQL "UPDATE tblOrderDetails LEFT JOIN tblDiscMatrix & _
ON (tblOrderDetails.AR_Matrix = tblDiscMatrix.AR_Matrix) & _
AND (tblOrderDetails.AR_Code = tblDiscMatrix.AR_Code) & _
SET tblOrderDetails.Multiply = [tblDiscMatrix].[Discount]
WHERE tblOrderDetails.QuoteID = QuoteID; """
 
You need to terminate each string before you continue to another line:

DoCmd.RunSQL "UPDATE tblOrderDetails LEFT JOIN tblDiscMatrix " & _
"ON (tblOrderDetails.AR_Matrix = tblDiscMatrix.AR_Matrix) " & _
"AND (tblOrderDetails.AR_Code = tblDiscMatrix.AR_Code) "& _
"SET tblOrderDetails.Multiply = [tblDiscMatrix].[Discount] " & _
"WHERE tblOrderDetails.QuoteID = QuoteID"

What's QuoteID, btw? If it's a variable, it needs to go outside the quotes:
that last line would be either

"WHERE tblOrderDetails.QuoteID = " & QuoteID

if QuoteID is numeric, or

"WHERE tblOrderDetails.QuoteID = '" & QuoteID & "'"

if it's text.
 
Back
Top