asked on

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

ASKER CERTIFIED SOLUTION

Eric Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

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

Open in new window

You have to double each double quote being inside the string.

ExpressMan1

ASKER

Perfect! No longer red. Thank You.

Qlemo

But still wrong, probably. Do you want to insert the evaluated DSum into the UPDATE, or let the UPDATE do the calculation?

ExpressMan1

ASKER

Let the UPDATE do the calculation. The field tblRate.AddlFuelChargeable is updating as planned.

Thanks to both of you.

Qlemo

http:#a40621647 calculates the value, then adds it as a literal. But if it works for you ...