The use of "_" in vba always get error

Question

My SQL query is really long so I tried to use _ to make them in separate lines. But I always got "compile error: Expected: end of statement" at "FROM" in the 3rd line. But I don't know where I got it wrong. Thanks everyone.

sqlString = _
"SELECT ISNULL([Yes Def],0), ISNULL(totalcount,0), ISNULL(([Yes Def]+0.0)/(totalcount+0.0) * 100,0) _
FROM [WAREHOUSE].[dbo].[SurveyQuestionDim] _
left outer join _
( _
SELECT SurveyQuestionKey,  sum(case when Response = 'Yes, definitely' then 1 else 0 end) as [Yes Def], count(1) as totalcount _
FROM [WAREHOUSE].[dbo].[SurveyAnswerFact] _
WHERE ([providerkey] = 26387) _
AND ([SurveyQuestionKey] IN (470,477,479,481,483)) _
AND ([ResponseDateKey] BETWEEN 20140601 AND 20150531) _
GROUP BY SurveyQuestionKey ) t1 _
on t1.[SurveyQuestionKey] = [WAREHOUSE].[dbo].[SurveyQuestionDim].[SurveyQuestionKey] _
WHERE [WAREHOUSE].[dbo].[SurveyQuestionDim].[SurveyQuestionKey] IN (470,477,479,481,483) _
ORDER BY [WAREHOUSE].[dbo].[SurveyQuestionDim].[SurveyQuestionKey] "

ivan · Accepted Answer · 2019-08-14T15:23:26.267

4

The _ character continues the line so you can use & to join more strings.

Have you tried it doing it like this?

  sqlString = "SELECT ISNULL([Yes Def],0), ISNULL(totalcount,0)," _
    & " ISNULL(([Yes Def]+0.0)/(totalcount+0.0) * 100,0) " _
    & " FROM [WAREHOUSE].[dbo].[SurveyQuestionDim]" _
    & " LEFT OUTER JOIN"

Another example:

"Data Source=1xx.1xx.1.1xx\SERVER123,1433;Initial Catalog=DB;" _
    & "Persist Security Info=True;User ID=ANYID;Password="

You have to have a space before the _ to continue the line and then the & concatenates the next line.

edited Aug 14 '19 at 15:23

answered Jul 21 '15 at 21:23

ivan

1,177
8
23

I just did... it didn't work...I think I'll just have to do a one laaarrrrrgggeee single line... Thank you! – JJJJ Jul 21 '15 at 21:29
yeap just tested it with the + instead of the "_" it does works for me in VB you just have to add the " in every string separating each line by a + ill edit my answer to show you an example good luck btw – ivan Jul 21 '15 at 21:30
1

That's correct, ivan, with some corrections noted here: 1) Use & rather than + for string concatenation. 2) use the _ to continue the line. – Don Jewett Jul 21 '15 at 21:32
nah, go ahead all yours :P – ivan Jul 21 '15 at 21:33
You may also want to mention using vbCrLf to format the SQL with lines, which it appears OP is trying to do. – Don Jewett Jul 21 '15 at 21:33
heres the vbCrLf info i read it a while ago... http://stackoverflow.com/a/27223391/5063263 – ivan Jul 21 '15 at 21:36
thanks for the edit. i really need to learn how to keep my work clean – ivan Jul 21 '15 at 21:46
1

I suggest you always begin your continued lines with a space e.g. & " FROM... " It is very easy to not see a missing space at the end of a line that may be off screen. – rheitzman Jul 21 '15 at 21:52
No problem. You might also want to break that long line so it doesn't scroll. The answer kind of gets lost. – Don Jewett Jul 21 '15 at 21:52

score 1 · Answer 2 · edited Jul 21 '15 at 21:47

1

As noted in the MSDN Reference, you can't continue in the middle of a string. You must continue the line and concatenate the strings. Therefore you need to use both the string concatenation character & and the line continuation character _.

sqlString = _
       "text text text text text text text text " & _
       "text text text text text text text text " & _
       "text text text text text text"

edited Jul 21 '15 at 21:47

shoover

3,071
1
29
40

answered Jul 21 '15 at 21:33

Tim Williams

154,628
8
97
125

2

IMO placing the & on the following line makes it clearer what is happening, especially to a new coder. Then the _ visually does what it logically does (break the line) and the & visually does what it logically does, concatenate the following string. – Don Jewett Jul 21 '15 at 21:50

score 1 · Answer 3 · answered Jul 21 '15 at 22:32

This is how I do it, it keeps it really nicely formatted so if I need to drop it in a query analyser it is formatted.

    sqlText = "Select trim(customer.cmpy_code) || trim(customer.cust_code) cust_code,"
    sqlText = sqlText & vbLf & "    trim(Customer.name_text) name_text,"
    sqlText = sqlText & vbLf & "    salesstat.ord_ind,"
    sqlText = sqlText & vbLf & "    sum(salesstat.net_amt-salesstat.return_net_amt) Gross_Revenue"
    sqlText = sqlText & vbLf & "from    Customer,"
    sqlText = sqlText & vbLf & "    salesstat,"
    sqlText = sqlText & vbLf & "    warereptdetl"
    sqlText = sqlText & vbLf & "Where   salesstat.cmpy_code = '" & QueryType & "'"
    sqlText = sqlText & vbLf & "and customer.cmpy_code = '" & QueryType & "'"
    sqlText = sqlText & vbLf & "and customer.cust_code = salesstat.cust_code"
    sqlText = sqlText & vbLf & "and salesstat.rept_code = '0'"

score 0 · Answer 4 · answered Aug 02 '16 at 16:03

Even though this question has already been answered, I just want to suggest to anyone viewing this post to use Stored Procedures as opposed to writing large SQL queries as strings in C# or VB code.

How it would be done in this specific example:

CREATE PROCEDURE ProcedureName AS 
SELECT ISNULL([Yes Def],0), ISNULL(totalcount,0), 
ISNULL(([Yes Def]+0.0)/(totalcount+0.0) * 100,0) 
FROM [WAREHOUSE].[dbo].[SurveyQuestionDim] 
LEFT OUTER JOIN (SELECT SurveyQuestionKey, SUM(CASE WHEN Response = 
'Yes, definitely' THEN 1 ELSE 0 END) AS [Yes Def], COUNT(1) AS totalcount 
FROM [WAREHOUSE].[dbo].[SurveyAnswerFact] WHERE ([providerkey] = 26387) 
AND ([SurveyQuestionKey] IN (470,477,479,481,483)) 
AND ([ResponseDateKey] BETWEEN 20140601 AND 20150531) 
GROUP BY SurveyQuestionKey ) t1 ON t1.[SurveyQuestionKey] = 
[WAREHOUSE].[dbo].[SurveyQuestionDim].[SurveyQuestionKey] 
WHERE [WAREHOUSE].[dbo].[SurveyQuestionDim].[SurveyQuestionKey] IN 
(470,477,479,481,483) ORDER BY [WAREHOUSE].[dbo].[SurveyQuestionDim].[SurveyQuestionKey]

The use of "_" in vba always get error

4 Answers4