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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.