MultiSelect Textbox

[Mike]

Im pulling some data from an acces database that looks like this:

Tax-82.54 Disc-0 Online-206.50
Instant-503.00‡CC-146‡‡Visa-139.14‡MC-‡Debit-187.26‡‡PM-3.00‡RJR-‡Lor-‡S&M-‡USST-7.00‡Other-‡Total Coupons-10.00‡Safe Fund- 300.00

How can I get it to look like this:
Tax-82.54 Disc-0 Online-206.50 Instant-503.00
CC-146

Visa-139.14
MC-
Debit-187.26

PM-3.00
RJR-
Lor-
S&M-
USST-7.00
Other-
Total Coupons-10.00

 

[Rick Rothstein]

Where is the information you are pulling in located at... In a variable? In
3 variables (one per line)? A cell on a worksheet? Three cells (one per
line) on a worksheet? The Textbox itself? An array variable? Some place
else? Where do you want the split apart text to be placed... the TextBox?

 

[Mike]

Rick,
The information is located in a field of an access database. Im using
ADODB/SQL query to get the data out of the database. I would like to put it
into the Textbox.

 

[Rick Rothstein]

I don't do databases, so I can't address that part at all. When you query
the database, the result of that query goes somewhere... my question is
where? We can pull the text apart relatively easily, but I need to know
where the text is at (that is, where can VB see it at) so I can manipulate
it? Do you put the result of the query in a variable, array, worksheet, the
TextBox, somewhere else?

 

[Mike]

Rick,
I can put results it into the textbox then manipulate it from there or how
ever you think would be eaisier. Variable maybe ?? Im not sure.

 

[Rick Rothstein]

Assuming your query text is in the TextBox (if not, substitute your
variable's name for the TextBox1.Text in the first assignment statement to
the Txt variable), here is the code that will do what you asked...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
Txt = Left(Txt, InStrRev(Txt, vbLf) - 1)
TextBox1.Text = Txt

You can execute it by whatever means you want (perhaps a CommandButton Click
event).

 

[Mike]

Rick
Here is what I have but it is leaving off the last part of the string
"Safe Fund- 300.00"
Any help would be great.

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00‡CC-146‡‡Visa-139.14‡MC-‡Debit-187.26‡‡PM-3.00‡RJR-‡Lor-‡S&M-‡USST-7.00‡Other-‡Total
Coupons-10.00‡Safe Fund- 300.00"

With ListBox1
..Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) <> 135 Then
sO = sO + Mid(sT, iC, 1)
Else
..AddItem sO
sO = ""
End If
Next
End With
End Sub

 

[Rick Rothstein]

I left that part off on purpose because your first post didn't show it in
the "what I want it to look like afterwards" section... removing the
next-to-last statement will allow that part of the text to show...

Dim Txt As String
Dim Delimiter As String
Delimiter = Chr(135)
Txt = Replace(Replace(TextBox1.Text, vbCr, " "), vbLf, " ")
Txt = Application.WorksheetFunction.Trim(Txt)
Txt = Replace(Txt, Delimiter & Delimiter, vbLf & vbLf)
Txt = Replace(Txt, Delimiter, vbLf)
TextBox1.Text = Txt

I see you also switched from a TextBox (again, mentioned in your first post)
to a ListBox. I'm not sure if that was a desired change or only one you made
to try and solve the problem you were having. The above code assumes you
still want to use a TextBox. If not, write back and I'll modify it for a
ListBox.

 

[Mike]

'Here is where im at. The For iC = 1 To Len(Trim(sT)) loop is leaving off the
end of the sting "Safe Fund- 300.00" Yes I have switched to a Multiline
texted box so to be able to modify and push the results back to the database.
And Im really doing this as an .exe program with vb.net 2005.
'sT = Tax-82.54 Disc-0 Online-206.50
'Instant-503.00‡CC-146‡‡Visa-139.14‡MC-‡Debit-187.26‡‡PM-3.00‡RJR-‡Lor-‡S&M-‡USST-7.00‡Other-‡Total Coupons-'10.00‡Safe Fund- 300.00

sSQL = "SELECT RegReconcile.* FROM RegReconcile
WHERE(((RegReconcile.REG_Z_COUNTER)=" & sRegisterZ & "));"
rs = New ADODB.Recordset
rs.Open(sSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly,
ADODB.LockTypeEnum.adLockReadOnly)

If (Not rs.EOF) Then
sT = rs.Fields("Z_NOTES").Value
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) <> 135 Then
sO = sO + Mid(sT, iC, 1)
Else
sZNotes = sZNotes & sO & vbCrLf
sO = ""
End If
Next
rs.Close()
rs = Nothing
With Me.txtNotes
.Text = sZNotes
End With
End If

 

[Rick Rothstein]

Uh, you might have considered mentioning the VB.NET 2005 part a lot sooner
in this thread... it is a **major** factor. The Excel programming this
newsgroup tends to handle is the VBA version of VB6 built into Excel... VB6
and VB.NET (any version) are pretty much as different from each other as
night and day. The type of parsing you are doing does not really need Excel
at all, so I'm thinking you might be better served asking your question in a
newsgroup devoted to the .NET version of VB rather than here in an Excel
oriented newsgroup. (I don't know the .NET version of VB, so I can't help
you with that part at all.) You best bet is to look for newsgroups with the
word "dotnet" in their names. Some examples...

microsoft.public.dotnet.general
microsoft.public.dotnet.languages.vb

 

[Mike]

Rick
You maybe right but why wont this work in excel then ??? It leaves off "Safe
Fund- 300.00"

Test and see for yourself

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00‡CC-146‡‡Visa-139.14‡MC-‡Debit-187.26‡‡PM-3.00‡RJR-‡Lor-‡S&M-‡USST-7.00‡Other-‡Total
Coupons-10.00‡Safe Fund- 300.00"

With ListBox1
..Clear
For iC = 1 To Len(Trim(sT))
If Asc(Mid(sT, iC, 1)) <> 135 Then
sO = sO + Mid(sT, iC, 1)
Else
..AddItem sO
sO = ""
End If
Next
End With
End Sub

 

[Rick Rothstein]

Because your instruction to place the text in the ListBox is conditioned on
finding the character with ASCII code 135... that character does not exist
at the end of your text string so the last formed text string is never
written out to the ListBox. Here is a modification to your code that handles
the problem...

Sub FillList()
Dim sT As String, sO As String
Dim iC As Integer

sT = "Tax-82.54 Disc-0 Online-206.50
Instant-503.00‡CC-146‡‡Visa-139.14‡MC-‡Debit-187.26‡‡PM-3.00‡RJR-‡Lor-‡S&M-‡USST-7.00‡Other-‡Total
Coupons-10.00‡Safe Fund- 300.00"

With ListBox1
.Clear
For iC = 1 To Len(Trim(sT) & Chr(135))
If Asc(Mid(sT & Chr(135), iC, 1)) <> 135 Then
sO = sO + Mid(sT, iC, 1)
Else
.AddItem sO
sO = ""
End If
Next
End With
End Sub