Access VBA SQL Syntax Error for Line continuation

I made the following DSum Update query in the editor and pasted into VBA using the   " &  _   for line continuation. Code turns red.  Tried two different blocks as below. Only the third line in the first block below turns red. Can I use DSUM in VBA like this?

strSQL7 = strSQL7 & " UPDATE tblRateCharges INNER JOIN tblRate ON"
strSQL7 = strSQL7 & " tblRateCharges.RateID = tblRate.RateID SET"
strSQL7 = strSQL7 & " tblRate.AddlFuelChargeable = DSum("Charge","tblRateCharges","FuelChargeable= -1")"    

strSQL7 = "UPDATE tblRateCharges INNER JOIN tblRate ON " & _
        "tblRateCharges.RateID = tblRate.RateID SET " & _
        "tblRate.AddlFuelChargeable = DSum("Charge","tblRateCharges","FuelChargeable= True")"
ExpressMan1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Eric ShermanConnect With a Mentor Accountant/DeveloperCommented:
Try this ...

strSQL7 = strSQL7 & " tblRate.AddlFuelChargeable = " & DSum("Charge","tblRateCharges","FuelChargeable= -1")  

ET
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
strSQL7 = strSQL7 & " tblRate.AddlFuelChargeable = DSum(""Charge"",""tblRateCharges"",""FuelChargeable= -1"")"     

Open in new window

You have to double each double quote being inside the string.
0
 
ExpressMan1Author Commented:
Perfect! No longer red.  Thank You.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
But still wrong, probably. Do you want to insert the evaluated DSum into the UPDATE, or let the UPDATE do the calculation?
0
 
ExpressMan1Author Commented:
Let the UPDATE do the calculation.  The field tblRate.AddlFuelChargeable is updating as planned.

Thanks to both of you.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
http:#a40621647 calculates the value, then adds it as a literal. But if it works for you ...
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.