Solved

Access VBA SQL Syntax Error for Line continuation

Posted on 2015-02-20
6
119 Views
Last Modified: 2016-02-10
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
Comment
Question by:ExpressMan1
  • 3
  • 2
6 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 40621647
Try this ...

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

ET
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40621713
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
 

Author Closing Comment

by:ExpressMan1
ID: 40621723
Perfect! No longer red.  Thank You.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 68

Expert Comment

by:Qlemo
ID: 40621731
But still wrong, probably. Do you want to insert the evaluated DSum into the UPDATE, or let the UPDATE do the calculation?
0
 

Author Comment

by:ExpressMan1
ID: 40621799
Let the UPDATE do the calculation.  The field tblRate.AddlFuelChargeable is updating as planned.

Thanks to both of you.
0
 
LVL 68

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now