• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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")"
0
ExpressMan1
Asked:
ExpressMan1
  • 3
  • 2
1 Solution
 
Eric ShermanAccountant/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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now