Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

string manipulation problems

Status
Not open for further replies.

csiszgab

Programmer
Joined
Oct 2, 2009
Messages
1
Location
HU
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:

Code:
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:

Code:
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.



Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top