VBA SQL help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mattc66 - 17 Nov 2006 23:35 GMT

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

Signature

Matt Campbell
mattc (at) saunatec [dot] com

Reply to this Message

Douglas J. Steele - 18 Nov 2006 03:42 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am trying to run the below SQL statement in code. VBA doesn't like it. I
>am
[quoted text clipped - 9 lines]
> SET tblOrderDetails.Multiply = [tblDiscMatrix].[Discount]
> WHERE tblOrderDetails.QuoteID = QuoteID; """

Reply to this Message