Line break in Excel VBA

what is the correct syntax to break up the extra lengthy string within VBA with Excel 2010.  I want to wrap this text so it is easier to read - just like MZ tools use to do.

Thanks,

K
         .commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext8 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Queryposttext8

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
The underscore is used in VBA as a line continuation character:
.commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext8 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Queryposttext8

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
thanks that helped accept now my results are not returning the correct value.

I am currently getting a duplicate:

and call_failure.failure_date >= '2014-07-09' and call_failure.failure_date <= '2014-08-21' '2014-07-09' and call_failure.failure_date <= '2014-08-21'

Got any ideas where it is duplicating within the string?
0
Karen SchaeferBI ANALYSTAuthor Commented:
Here is the code:
Sub RefreshQuery()
 Dim Querypretext1 As String
 Dim Queryposttext1 As String
 Dim valueToFilter As String
 Dim valueToFilter2 As String
 Dim ValueToFilter3 As String
 Dim Comtext As String
 Dim Querypretext2 As String
 Dim Queryposttext2 As String
 Dim Querypretext3 As String
 Dim Queryposttext3 As String
 Dim Querypretext4 As String
 Dim Queryposttext4 As String
 Dim Querypretext5 As String
 Dim Queryposttext5 As String
 Dim Querypretext6 As String
 Dim Queryposttext6 As String
 Dim Querypretext7 As String
 Dim Queryposttext7 As String
 Dim Querypretext8 As String
 Dim Queryposttext8 As String
  
 
 valueToFilter = "call_failure.failure_date >="
 valueToFilter2 = "call_failure.failure_date <="
 ValueToFilter3 = " and call_failure.failure_date <="

 With ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net drivetest").OLEDBConnection
      
     Comtext = .commandtext
     Queryposttext1 = Queryparam(Comtext, valueToFilter, valueToFilter2, Querypretext1)
     Queryposttext2 = Queryparam(Queryposttext1, valueToFilter, valueToFilter2, Querypretext2)
     Queryposttext3 = Queryparam(Queryposttext2, valueToFilter, valueToFilter2, Querypretext3)
     Queryposttext4 = Queryparam(Queryposttext3, valueToFilter, valueToFilter2, Querypretext4)
     Queryposttext5 = Queryparam(Queryposttext4, valueToFilter, valueToFilter2, Querypretext5)
     Queryposttext6 = Queryparam(Queryposttext5, valueToFilter, valueToFilter2, Querypretext6)
     Queryposttext7 = Queryparam(Queryposttext6, valueToFilter, valueToFilter2, Querypretext7)
     Queryposttext8 = Queryparam(Queryposttext7, valueToFilter, valueToFilter2, Querypretext8)
      
     
    ' queryPreText1 = .commandtext
   '  paramPosition1 = InStr(Right(.commandtext, Len(.commandtext) - InStrRev(.commandtext, "AND")), valueToFilter) + Len(valueToFilter) - 1 + InStr(.commandtext, "AND")
  '   queryPreText1 = Left(queryPreText1, paramPosition1)
  '   queryPostText1 = .commandtext
  '   Postlen = Len(queryPostText1) - paramPosition1 - (Len(Range("H3").Value) + 3)
   '  queryPostText1 = Right(queryPostText1, Postlen)
     
    ' queryPreText2 = queryPostText1
    ' paramPosition2 = InStr(Right(queryPostText1, Len(queryPostText1) - InStrRev(queryPostText1, "AND")), valueToFilter) + Len(valueToFilter) - 1 + InStr(queryPostText1, "AND")
   '  queryPreText2 = Left(queryPreText2, paramPosition2)
     
     
     
 '    queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
    ' .commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Queryposttext7
         .commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext8 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Queryposttext8
    Debug.Print Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext8 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value
                ' & "'" & Queryposttext8

 End With
 ActiveWorkbook.Connections("database name").Refresh
End Sub

Open in new window

0
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).

Glenn RayExcel VBA DeveloperCommented:
1) where is Querypretext.. assigned?

I am wondering if you could greatly simplify this code by assigning your substrings to arrays, like so:
Sub RefreshQuery()
    Dim strQueryPreText(8) As String
    Dim strQueryPostText(8) As String
    Dim strValueToFilter(3) As String
    Dim strComtext As String
    Dim x As Integer
    
    strValueToFilter(1) = "call_failure.failure_date >="
    strValueToFilter(2) = "call_failure.failure_date <="
    strValueToFilter(3) = " and call_failure.failure_date <="
    
    With ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net drivetest").OLEDBConnection
        strComtext = .CommandText
        strQueryPostText(1) = QueryParam(strComtext, strValueToFilter(1), strValueToFilter(2), strQueryPreText(1))
        For x = 2 To 8
            strQueryPostText(x) = QueryParam(strQueryPostText(x - 1), strValueToFilter(1), strValueToFilter(2), strQueryPreText(x))
        Next x
        For x = 1 To 8
            .CommandText = .CommandText & " " & strQueryPreText(x) & " '" & Range("H3").Value & "'" & strValueToFilter(3) & " ' " & Range("H4").Value & "' "
        Next x
       Debug.Print .CommandText
    
    End With
    ActiveWorkbook.Connections("database name").Refresh
End Sub

Open in new window


Regards,
-Glenn
0
Shaun KlineLead Software EngineerCommented:
Is that the entire output from the Debug.Print statement? There should be eight sets of query statements. You should print out the Querypretest variables to verify they contain the values you are expecting.
0
Karen SchaeferBI ANALYSTAuthor Commented:
Glen,

thanks again, however still getting dual dates:

select standardized_issue_category as reportgroup,  count(call_failure.id) as Occurence ,failure_date,
left(cluster,3) as cluster, is_dropped_call,project_name, standardized_issue_category as defect_id, platform_name, subcluster
from call_failure where standardized_issue_category = 'Traces Unavailable'
and defect_id is null and other_defect is null
and (tracking_status <> 'On Hold' or tracking_status is null)
and call_failure.failure_date >= '2014-07-09' and call_failure.failure_date <= ' 2014-08-22'   '2014-07-09' and call_failure.failure_date <= '2014-08-21' '2014-07-09' and call_failure.failure_date <= '2014-08-21'
group by standardized_issue_category  , failure_date,cluster, is_dropped_call,project_name,defect_id, platform_name, subcluster

union
select standardized_issue_category as reportgroup,  count(call_failure.id) as Occurence ,failure_date,
left(cluster,3) as cluster, is_dropped_call,project_name, standardized_issue_category as defect_id, platform_name, subcluster
from call_failure where standardized_issue_category = 'Traces Unavailable'
and defect_id is null and other_defect is null
and (tracking_status <> 'On Hold' or tracking_status is null)
and call_failure.failure_date >= '2014-07-09' and call_failure.failure_date <= ' 2014-08-22'
0
Karen SchaeferBI ANALYSTAuthor Commented:
and also getting error:

Application defined or object defined error on the :
        .commandtext = .commandtext & " " & strQueryPreText(x) & " '" & Range("H3").Value & "'" & strValueToFilter(3) & " ' " & Range("H4").Value & "' "

portion of the code.
0
Shaun KlineLead Software EngineerCommented:
Put a break point on the line with the error and view the object values in either the Immediate window or add a watch for each variable.

Also, to reiterate, please print out the strQueryPreText variables to verify they contain the values you are expecting. My guess is that your QueryParam function is not returning the strQueryPreText values that you are expecting.
0
Glenn RayExcel VBA DeveloperCommented:
Another thing to check is the actual number of strQueryPreText values being assigned.  It looks like in your example only three values (identical) are assigned.  The loop method could be modified to use UBound(strQueryPreText) to limit it.

Can you please state what the values in cells H3 and H4 are?  I assume they're dates, but want to be sure.

And is this statement the .CommandText value at the start (i.e., for your OLEDB connection)?
Select standardized_issue_category as reportgroup,  count(call_failure.id) as Occurence ,failure_date,
 left(cluster,3) as cluster, is_dropped_call,project_name, standardized_issue_category as defect_id, platform_name, subcluster
 from call_failure where standardized_issue_category = 'Traces Unavailable'
 and defect_id is null and other_defect is null
 and (tracking_status <> 'On Hold' or tracking_status is null)
0
Karen SchaeferBI ANALYSTAuthor Commented:
Well the original Sql Statment contains 8 Select statements via the Unions, however, the current code is duplicating its select statements.

Please keep in mind I inherited the original code and I need to improve the processing of the query which sets the Properties of the Data Connection.  The current code fails due to the duplication of the Sql statement.

so somewhere in the code it its inputted the original data connections property and then adding the revised statement.  causing the error.

"Can you please state what the values in cells H3 and H4 are?  I assume they're dates, but want to be sure."  
Yes they are the dates which are inputted on the form.

However after review of the step thru code , it appears I just need to remove the existing property value for the datasource and replace it with the updated with the correct dates.  How would replace the sql statement on the fly?

thanks for the assistance

Sub RefreshQuery()
    Dim strQueryPreText(8) As String
    Dim strQueryPostText(8) As String
    Dim strValueToFilter(3) As String
    Dim strComtext As String
    Dim x As Integer
    
    strValueToFilter(1) = "call_failure.failure_date >="
    strValueToFilter(2) = "call_failure.failure_date <="
    strValueToFilter(3) = " and call_failure.failure_date <="
    
    With ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net drivetest").OLEDBConnection
        strComtext = .commandtext
        strQueryPostText(1) = Queryparam(strComtext, strValueToFilter(1), strValueToFilter(2), strQueryPreText(1))
        For x = 2 To 8
            strQueryPostText(x) = Queryparam(strQueryPostText(x - 1), strValueToFilter(1), strValueToFilter(2), strQueryPreText(x))
        Next x
        'Debug.Print strQueryPostText(x)
        For x = 1 To 8
            .commandtext = .commandtext & " " & strQueryPreText(x) & " '" & Range("H3").Value & "'" & strValueToFilter(3) & " ' " & Range("H4").Value & "' "
        Next x
       Debug.Print .commandtext
    
    End With
 ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net drivetest").Refresh
End Sub

Open in new window


Sub RefreshQuery_daily()
 Dim Querypretext1 As String
 Dim Queryposttext1 As String
 Dim valueToFilter As String
 Dim valueToFilter2 As String
 Dim Comtext As String
 Dim Querypretext2 As String
 Dim Queryposttext2 As String
  
 
 valueToFilter = "date_of_daily >="
 valueToFilter2 = "date_of_daily <="
 ValueToFilter3 = " and date_of_daily <="

 With ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net master_failure_daily").OLEDBConnection
      
     Comtext = .commandtext
     Queryposttext1 = Queryparam(Comtext, valueToFilter, valueToFilter2, Querypretext1)
           
     .commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Queryposttext1
 End With
 ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net master_failure_daily").Refresh
End Sub

Open in new window

Function Queryparam(commandtext As String, valueToFilter As String, valueToFilter2 As String, ByRef Querypretext As String)
Dim paramPosition1 As Integer
Dim paramPosition2 As Integer
 
     Querypretext = commandtext
     paramPosition1 = InStr(Right(commandtext, Len(commandtext) - InStrRev(commandtext, "AND")), valueToFilter) + Len(valueToFilter) - 1 + InStr(commandtext, "AND")
     Querypretext = left(Querypretext, paramPosition1)
     paramPosition2 = InStr(Right(commandtext, Len(commandtext) - InStrRev(commandtext, "AND")), valueToFilter2) + Len(valueToFilter2) - 1 + InStr(commandtext, "AND")
     queryPostText = commandtext
     Postlen = Len(queryPostText) - paramPosition2 - 13
     queryPostText = Right(queryPostText, Postlen)
     Queryparam = queryPostText
'     Debug.Print Queryparam
End Function

Open in new window

0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.