string manipulation problem

[csiszarg]

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 excel 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

[Bob Phillips]

Hi Gabor,

I am afarid I have absolutely no idea what the problem is, but I also have no idea what you are trying to do. But loading a 4-5K string into a textbox doesn't seem overly smart to me, how can anyone possibly absorb that there. I would suggest the whole approach needs a re-think.

[csiszarg]

Hi Xld,

Meanwhile I solved the problem myself. The problem lied in the fact that when I assign value to a textbox, like this:

 
For i = 1 To SS.NbRows
    If LBFrom.List(LBFrom.ListIndex) = SS.SelStat(i - 1, 1) Then
        AnFvParam_02.SelectLineTextBox.Text =SS.SelStat(i - 1, 0)
    End If
Next i

Then VBA attach an end of file character to the string (SS.SelStat(i-1,0)), which makes impossible any kind of concatenation, replacing, etc. So I needed to create a string to temporarily store the SS.SelStat(i-1,0) in it, and so I avoid this error.

Gabor

[Bob Phillips]

Solving the (immediate) problem doesn't change my advice, I think you are storing up problems.