Access VBA SQL Syntax

Have a syntax error in this Update query. Make query in query designer and pasted it into module with line continuation characters " & _  What am I doing wrong?

strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = " & _
                "DSum("Charge","tblRateCharges","FuelChargeable= -1 " & _
                "and DetailID= [Forms]!frmRateTool.[DetailID]") " & _
                 "WHERE (((tblRateDetails.DetailID)=[Forms]![frmRateTool]![DetailID]));"
ExpressMan1Asked:
Who is Participating?
 
Robert ShermanOwnerCommented:
strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = " & _
                 "DSum(""Charge"",""tblRateCharges"",""FuelChargeable= -1 " & _
                 "and DetailID= [Forms]!frmRateTool.[DetailID]"") " & _
                  " WHERE (tblRateDetails.DetailID)= " & [Forms]![frmRateTool]![DetailID]
0
 
Rey Obrero (Capricorn1)Commented:
strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = " & _
                 "DSum("Charge","tblRateCharges","FuelChargeable= -1 " & _
                 "and DetailID= [Forms]!frmRateTool.[DetailID]") " & _
                  " WHERE (tblRateDetails.DetailID)= " & [Forms]![frmRateTool]![DetailID]
0
 
SimonCommented:
If in doubt, take the line continuation characters out and check that it runs as a single line.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
ExpressMan1Author Commented:
Tried single line and Rey's code, get syntax error on both.

Here is the sql from the query designer which works.

UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = DSum("Charge","tblRateCharges","FuelChargeable= -1 and DetailID= [Forms]!frmRateTool.[DetailID]")
WHERE (((tblRateDetails.DetailID)=[Forms]![frmRateTool]![DetailID]));
0
 
aikimarkCommented:
Is the form open?

Depending on the circumstances, you might try this version of the SQL, which concatenates the form control value during string construction.
strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = " & _
                "DSum("Charge","tblRateCharges","FuelChargeable= -1 " & _
                "and DetailID=" & [Forms]!frmRateTool.[DetailID] & ") " & _
                "WHERE (((tblRateDetails.DetailID)=[Forms]![frmRateTool]![DetailID]));"

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
sorry, didn't see the Dsum, try this copy and paste

strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = DSum('Charge','tblRateCharges', 'FuelChargeable= -1" _
                  & " and DetailID= " & [Forms]!frmRateTool.[DetailID] & ")" _
                   & " WHERE (tblRateDetails.DetailID)= " & [Forms]![frmRateTool]![DetailID]
0
 
Robert ShermanOwnerCommented:
Essentially, the problem has to do with the quotation marks inside the string... I think I fixed all of them, but if you run into any issues with the version I just posted, look for any other instances where there is a quotation mark inside the string that I may have missed.... double quotation marks right next to each other take care of this problem by telling the compiler that you actually want a quotation mark and not that you are signalling the end of the string.
0
 
ExpressMan1Author Commented:
Works !! Extra quotation marks, now I know.

Thanks to all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.