string manipulation problems

[csiszgab]

Hi,

I am working with Business Objects VBA and user forms, but I didn't find a user form topic, so I post my message here in the office topic.

In order to solve an issue I have to manipulate a long string (more than a thousand character, but not reaching ten thousand character), which is an SQL statement.

The first problem I face with is the following: I load the parts of the select statement into a string array (which is a global string array declared in a module), and when I switch between the forms, suddenly I find that an end of file character is inserted at the end of each string in the string array, except for the first element. The declaration of the string is the following:

Type SelectStatementType
    NbRows As Integer
    SelStat(99, 2) As String
    DotPlace(99) As Integer
End Type
Global SS As SelectStatementType

The code I use for "string cleaning" is the following:

For i = 2 To SS.NbRows
    SS.SelStat(i - 1, 0) = Mid(SS.SelStat(i - 1, 0), 1, Len(SS.SelStat(i - 1, 0)) - 1)
Next

The next problem is that after I did the modifications on the appropriate parts of the select statement, I have to load back the whole SQL statement into a textbox. This SQL statement can be very long, reaching 4000-5000 character in length. Sometimes I can insert only the select statement and VBA don't concatenate it with the string after the select part (I call this part SQLAfterFrom clause). I copy here the code I use to create the sql statement.

selectstatement = ""
For i = 1 To SS.NbRows
    If i = 1 Then
        selectstatement = SS.SelStat(i - 1, 0)
    Else
        selectstatement = selectstatement & Chr(13) & "," & SS.SelStat(i - 1, 0)
    End If
Next
SQLAfterFrom = Mid(AnFvParam_02.SQLText.Text, InStr(AnFvParam_02.SQLText.Text, "FROM"))
SQLWholeText = Replace("SELECT " & selectstatement & SQLAfterFrom, "¶", Chr(13))
SQLText.Text = SQLWholeText
MsgBox (SQLWholeText)

Any advice on what I am doing wrong would be highly appreciated.

Thanks,
Gabor

[Hack]

Moved To Office Development