Continue VBA code onto new line

Question

I have written VBA code behind a CommandButton within Excel but seems like CommandText can only hold a certain amount of characters.

This is my code (on one line) :

.CommandText = "SELECT ID,Employee,WT,[Amount Per Kilometer],Currency,SUM([Number (Amount of km)]) AS [Number (Amount of km)],SUM([Total (per record)]) AS [Total (per record)] FROM (SELECT S.ID,S.FirstName + ' ' + S.LastName AS [Employee],C.Customer_Name,NULL AS [WT],EC.AA_Rate AS [Amount Per Kilometer],NULL AS [Currency],TS.Travel AS [Number (Amount of km)],TS.Travel * CONVERT(float,EC.AA_Rate) AS [Total (per record)] FROM [Timesheets].[dbo].[timesheets] TS INNER JOIN [Timesheets].[dbo].[staff] S ON TS.Staff_Code = S.Staff_Code INNER JOIN [Timesheets].[dbo].[enginecapacity] EC ON TS.EngineCapacityCode = EC.EngineCapacityCode INNER JOIN [Timesheets].[dbo].[customers] C ON TS.Cust_Code = C.Cust_Code WHERE TS.DateWorked BETWEEN LEFT('"& FromDate &"', 4) + SUBSTRING('"& FromDate &"',CHARINDEX('/','"& FromDate &"')+1,LEN('"& FromDate &"') - CHARINDEX('/','2014/12/31') - CHARINDEX('/',REVERSE('2014/12/31'))) + RIGHT('"& FromDate &"',2) AND LEFT('"& ToDate &"', 4) + SUBSTRING('"& ToDate &"',CHARINDEX('/','"& ToDate"')+1,LEN('"& ToDate &"') - CHARINDEX('/','"& ToDate &"') - CHARINDEX('/',REVERSE('"& ToDate &"'))) + RIGHT('"& ToDate &"',2)) as A GROUP BY ID,Employee,WT,[Amount Per Kilometer],Currency"

I need the above code to be split into 2 or 3 lines.

This is what I have tried :

.CommandText = "SELECT ID, " & Resp & _
        "Employee, " & Resp & _
        "WT, " & Resp & _
        "[Amount Per Kilometer], " & Resp & _
        "Currency, " & Resp & _
        "SUM([Number (Amount of km)]) AS [Number (Amount of km)], " & Resp & _
        "SUM([Total (per record)]) AS [Total (per record)] " & Resp & _ 
        "FROM ( " & Resp & _
        "SELECT S.ID, " & Resp & _
        "S.FirstName + ' ' + S.LastName AS [Employee], " & Resp & _
        "C.Customer_Name, " & Resp & _
        "NULL AS [WT], " & Resp & _
        "EC.AA_Rate AS [Amount Per Kilometer], " & Resp & _
        "NULL AS [Currency], " & Resp & _
        "TS.Travel AS [Number (Amount of km)], " & Resp & _
        "TS.Travel * CONVERT(float,EC.AA_Rate) AS [Total (per record)] " & Resp & _
        "FROM [Timesheets].[dbo].[timesheets] TS " & Resp & _ 
        "INNER JOIN [Timesheets].[dbo].[staff] S ON TS.Staff_Code = S.Staff_Code " & Resp & _
        "INNER JOIN [Timesheets].[dbo].[enginecapacity] EC ON TS.EngineCapacityCode = EC.EngineCapacityCode " & Resp & _ 
        "INNER JOIN [Timesheets].[dbo].[customers] C ON TS.Cust_Code = C.Cust_Code " & Resp & _
        "WHERE TS.DateWorked BETWEEN " & Resp & _
        "LEFT('"& FromDate &"', 4) + SUBSTRING('"& FromDate &"',CHARINDEX('/','"& FromDate &"')+1,LEN('"& FromDate &"') - CHARINDEX('/','2014/12/31') - CHARINDEX('/',REVERSE('2014/12/31'))) + RIGHT('"& FromDate &"',2) " & Resp & _ 
        "AND LEFT('"& ToDate &"', 4) + SUBSTRING('"& ToDate &"',CHARINDEX('/','"& ToDate"')+1,LEN('"& ToDate &"') - CHARINDEX('/','"& ToDate &"') - CHARINDEX('/',REVERSE('"& ToDate &"'))) + RIGHT('"& ToDate &"',2)) " & Resp & _ 
        "as A GROUP BY ID,Employee,WT,[Amount Per Kilometer],Currency"

I get a Syntax Error when running the above.

Any help would be greatly appreciated.

possible duplicate of [How to continue the code on the next line in VBA](http://stackoverflow.com/questions/22854386/how-to-continue-the-code-on-the-next-line-in-vba) — G_hi3, Aug 14 '15 at 04:53
@G_hi3 , Thank you for the link. I have seen that answer but still got errors when executed. Joehanna stopped the missing `&`. — PKirby, Aug 14 '15 at 05:08
I actually read the text wrong, I thought line breaks were not allowed after `&` operators — G_hi3, Aug 14 '15 at 05:11

score 2 · Accepted Answer · answered Aug 14 '15 at 05:04

2

The problem is a missing & after the 3rd ToDate in the second last line.

answered Aug 14 '15 at 05:04

joehanna

1,471
1
11
22

Continue VBA code onto new line

1 Answers1