Split Characters in Textbox

[ryguy7272]

A short time ago I saw an interesting post on this DG. I tried to run the
code, but no matter what I did, it didn’t work (I couldn’t get the code to
fire; nothing happed). Below is the VBA:

Const lngLimit As Long = 500

Dim lngCount As Long
Dim lngSplits As Long
Dim strArray() As String
Dim strText As String

strText = "String to split..."
lngSplits = Len(strText) \ lngLimit

If Len(strText) Mod lngLimit = 0 Then
lngSplits = lngSplits - 1
End If
ReDim strArray(0 To lngSplits)

For lngCount = 0 To lngSplits
strArray(lngCount) = Mid$(strText, lngCount * lngLimit + 1, lngLimit)
Next lngCount

I guess it is supposed to take text in a TextBox and parse it up into
segments of 500 characters, in the TextBox. Again, I can’t get it to work.
What am I supposed to do? Does the text fire from a Form? OD I need a Query
to parse it? How do I run the code?

Thanks,
Ryan---

 

[Douglas J. Steele]

That's an incomplete code snippet. If you typed that code into a module,
you'd get an error, since you haven't put it inside a function or sub.
Assuming you have put it into a function or sub, that function or sub would
have to be invoked in some way.

All the code snippet does is create an array based on the value in variable
strText. Each member of the array (except the last) will have 500 characters
in it. Unless you do something with the array, though, you wouldn't see
anything in your application once that code runs.

 

[ryguy7272]

I think I understand. Well, how would I invoke the code? How would I assign
the results to a TextBox? I'm definitely losing it there. A Query would
make sense; I've done that before, many different ways, but I've never parsed
long strings of text into TextBoxes, or multiple TextBoxes, or any such
thing. Can you please show me how to make the code do something useful. I
may not need this today or tomorrow, but I can forsee a day in the near
future where I would have a need for this type of code.


Thanks,
Ryan---

 

[Douglas J. Steele]

What are you trying to do that you think you need to split the text before
assigning the results to a text box?

Even if strText contains thousands of characters, you can still assign it to
a text box.

 

[ryguy7272]

Thanks for the reply, Doug. What is the limit in a TextBox? 65536
characters, right? Well, I was thinking of something a bit smaller, for my
needs...maybe chunks of 16 characters. I was going to try this on some
nucleotide sequences for a biophysics class that I am taking. My goal was
mostly to practice this with Access; I can do it in Excel easy enough. I am
trying to become more proficient in Access. The 500 was just an arbitrary
number; it just caught my eye the other day when I was reading through old
posts on the DG.

If you know how to make the code work, please do tell.

Thanks,
Ryan---

 

[Douglas J. Steele]

What's your definition of "working"?

As I stated, all it does is take the text and put it into an array. And
remember that the split is a hard "every 500 characters": that's not exactly
friendly for displaying in multiple text boxes.

If you've got, say, ten text boxes on your form named Text0, Text1, Text2,
.... , Text9 and a command button "cmdSplit", you can do something like:

Private Sub cmdSplit_Click()

Const lngLimit As Long = 500

Dim lngCount As Long
Dim lngSplits As Long
Dim strArray() As String
Dim strText As String

strText = "String to split..."
lngSplits = Len(strText) \ lngLimit

If Len(strText) Mod lngLimit = 0 Then
lngSplits = lngSplits - 1
End If
ReDim strArray(0 To lngSplits)

If lngSplits >= 10 Then
MsgBox "The text splits into " & lngSplits & " chunks, " & _
"which is too many to display."
End If

For lngCount = 0 To lngSplits
strArray(lngCount) = Mid$(strText, lngCount * lngLimit + 1, lngLimit)
If lngCount < 10 Then
Me.Controls("Text" & lngCount) = strArray(lngCount)
End If
Next lngCount

End Sub

 

[Dale Fye]

ryguy,

A standard text box will wrap text automatically, so I'm not entirely sure
why you would need to do that in a text box. However, I have a similiar
function that I use when I want to display text in a message box, and want it
to be a certain width.

Copy and Paste this function into a code module (make sure to name the
module something different than the function).

You could use this function anyplace (query, form, report) where you want to
force the word wrap to occur at (or close to) a specific character position.
It works great if you are using a non-proportional type font (where each
character takes up the same space) but is not as clean with proportional type
fonts.

As an example, lets assume you have a field in the recordset of a form. You
really don't want to edit this field, but you want to display it with a fixed
(or close) width. In this case, you might set the ControlSource of the
textbox to something like:

ControlSource: =WrapText([FieldName], 50, 3)

The WrapLength parameter defines how wide you want the text to be. The
Variance parameter determines how far prior to the WrapWidth the function
will start looking for a space

Public Function WrapText(ByVal SomeText As Variant, WrapLength As Integer, _
Optional Variance As Integer = 3) As String

Dim strText As String
Dim intCharPos As Integer, intCRLF As Integer

SomeText = Trim(SomeText)
Do While Len(SomeText & "") > 0
intCRLF = InStr(SomeText, vbCrLf)
If intCRLF > 0 And intCRLF < WrapLength Then
strText = Trim(Left(SomeText, intCRLF + 1))
SomeText = Mid(SomeText, intCRLF + 2)
Else
intCharPos = InStr(fnMin(Len(SomeText & ""), WrapLength -
Variance), SomeText, " ")
If intCharPos = 0 Then
strText = SomeText & ""
SomeText = ""
Else
strText = Trim(Left(SomeText, intCharPos))
SomeText = Mid(SomeText, intCharPos + 1)
End If
End If
If Len(WrapText) = 0 Then
WrapText = strText
ElseIf InStrRev(WrapText, vbCrLf) = Len(WrapText) Then
WrapText = WrapText & strText
Else
WrapText = WrapText & vbCrLf & strText
End If
Loop

End Function

In order to use this, you will also need to copy and paste the function
fnMin below:

Public Function fnMin(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal

End Function

 

[ryguy7272]

Thanks Dale! Works like a Swiss Watch! If a Query was a Swiss Watch.

Doug, where do I feed the data into the parser? I tried the code as is and
it did nothing. I changed this
strText = "String to split..."

To this:
strText = Table1

My data is stored in Table1. I think I have to point the parser there,
right. How else is it going to know where to look? When I run the macro now
I get 'Run-time Error 9: Subscript out of Range'. I can't see how to load
the data into the parser. I'm missing a critical step somewhere...

Sorry for being think here. Trying to learn this stuff so I can do it for
myself next time, and eventually help others, sometime in the future.

Thanks,
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

ryguy,

A standard text box will wrap text automatically, so I'm not entirely sure
why you would need to do that in a text box. However, I have a similiar
function that I use when I want to display text in a message box, and want it
to be a certain width.

Copy and Paste this function into a code module (make sure to name the
module something different than the function).

You could use this function anyplace (query, form, report) where you want to
force the word wrap to occur at (or close to) a specific character position.
It works great if you are using a non-proportional type font (where each
character takes up the same space) but is not as clean with proportional type
fonts.

As an example, lets assume you have a field in the recordset of a form. You
really don't want to edit this field, but you want to display it with a fixed
(or close) width. In this case, you might set the ControlSource of the
textbox to something like:

ControlSource: =WrapText([FieldName], 50, 3)

The WrapLength parameter defines how wide you want the text to be. The
Variance parameter determines how far prior to the WrapWidth the function
will start looking for a space

Public Function WrapText(ByVal SomeText As Variant, WrapLength As Integer, _
Optional Variance As Integer = 3) As String

Dim strText As String
Dim intCharPos As Integer, intCRLF As Integer

SomeText = Trim(SomeText)
Do While Len(SomeText & "") > 0
intCRLF = InStr(SomeText, vbCrLf)
If intCRLF > 0 And intCRLF < WrapLength Then
strText = Trim(Left(SomeText, intCRLF + 1))
SomeText = Mid(SomeText, intCRLF + 2)
Else
intCharPos = InStr(fnMin(Len(SomeText & ""), WrapLength -
Variance), SomeText, " ")
If intCharPos = 0 Then
strText = SomeText & ""
SomeText = ""
Else
strText = Trim(Left(SomeText, intCharPos))
SomeText = Mid(SomeText, intCharPos + 1)
End If
End If
If Len(WrapText) = 0 Then
WrapText = strText
ElseIf InStrRev(WrapText, vbCrLf) = Len(WrapText) Then
WrapText = WrapText & strText
Else
WrapText = WrapText & vbCrLf & strText
End If
Loop

End Function

In order to use this, you will also need to copy and paste the function
fnMin below:

Public Function fnMin(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal

End Function

----
HTH
Dale

A short time ago I saw an interesting post on this DG. I tried to run the
code, but no matter what I did, it didn’t work (I couldn’t get the code to
fire; nothing happed). Below is the VBA:

Const lngLimit As Long = 500

Dim lngCount As Long
Dim lngSplits As Long
Dim strArray() As String
Dim strText As String

strText = "String to split..."
lngSplits = Len(strText) \ lngLimit

If Len(strText) Mod lngLimit = 0 Then
lngSplits = lngSplits - 1
End If
ReDim strArray(0 To lngSplits)

For lngCount = 0 To lngSplits
strArray(lngCount) = Mid$(strText, lngCount * lngLimit + 1, lngLimit)
Next lngCount

I guess it is supposed to take text in a TextBox and parse it up into
segments of 500 characters, in the TextBox. Again, I can’t get it to work.
What am I supposed to do? Does the text fire from a Form? OD I need a Query
to parse it? How do I run the code?

Thanks,
Ryan---

 

[Douglas J. Steele]

No, it's not as simple as that. That code assumes you're passing it a single
value as strText.

Perhaps you should step back and describe what it is you're trying to do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Dale! Works like a Swiss Watch! If a Query was a Swiss Watch.

Doug, where do I feed the data into the parser? I tried the code as is
and
it did nothing. I changed this
strText = "String to split..."

To this:
strText = Table1

My data is stored in Table1. I think I have to point the parser there,
right. How else is it going to know where to look? When I run the macro
now
I get 'Run-time Error 9: Subscript out of Range'. I can't see how to
load
the data into the parser. I'm missing a critical step somewhere...

Sorry for being think here. Trying to learn this stuff so I can do it for
myself next time, and eventually help others, sometime in the future.

Thanks,
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

ryguy,

A standard text box will wrap text automatically, so I'm not entirely
sure
why you would need to do that in a text box. However, I have a similiar
function that I use when I want to display text in a message box, and
want it
to be a certain width.

Copy and Paste this function into a code module (make sure to name the
module something different than the function).

You could use this function anyplace (query, form, report) where you want
to
force the word wrap to occur at (or close to) a specific character
position.
It works great if you are using a non-proportional type font (where each
character takes up the same space) but is not as clean with proportional
type
fonts.

As an example, lets assume you have a field in the recordset of a form.
You
really don't want to edit this field, but you want to display it with a
fixed
(or close) width. In this case, you might set the ControlSource of the
textbox to something like:

ControlSource: =WrapText([FieldName], 50, 3)

The WrapLength parameter defines how wide you want the text to be. The
Variance parameter determines how far prior to the WrapWidth the function
will start looking for a space

Public Function WrapText(ByVal SomeText As Variant, WrapLength As
Integer, _
Optional Variance As Integer = 3) As String

Dim strText As String
Dim intCharPos As Integer, intCRLF As Integer

SomeText = Trim(SomeText)
Do While Len(SomeText & "") > 0
intCRLF = InStr(SomeText, vbCrLf)
If intCRLF > 0 And intCRLF < WrapLength Then
strText = Trim(Left(SomeText, intCRLF + 1))
SomeText = Mid(SomeText, intCRLF + 2)
Else
intCharPos = InStr(fnMin(Len(SomeText & ""), WrapLength -
Variance), SomeText, " ")
If intCharPos = 0 Then
strText = SomeText & ""
SomeText = ""
Else
strText = Trim(Left(SomeText, intCharPos))
SomeText = Mid(SomeText, intCharPos + 1)
End If
End If
If Len(WrapText) = 0 Then
WrapText = strText
ElseIf InStrRev(WrapText, vbCrLf) = Len(WrapText) Then
WrapText = WrapText & strText
Else
WrapText = WrapText & vbCrLf & strText
End If
Loop

End Function

In order to use this, you will also need to copy and paste the function
fnMin below:

Public Function fnMin(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal

End Function

----
HTH
Dale

A short time ago I saw an interesting post on this DG. I tried to run
the
code, but no matter what I did, it didn't work (I couldn't get the code
to
fire; nothing happed). Below is the VBA:

Const lngLimit As Long = 500

Dim lngCount As Long
Dim lngSplits As Long
Dim strArray() As String
Dim strText As String

strText = "String to split..."
lngSplits = Len(strText) \ lngLimit

If Len(strText) Mod lngLimit = 0 Then
lngSplits = lngSplits - 1
End If
ReDim strArray(0 To lngSplits)

For lngCount = 0 To lngSplits
strArray(lngCount) = Mid$(strText, lngCount * lngLimit + 1, lngLimit)
Next lngCount

I guess it is supposed to take text in a TextBox and parse it up into
segments of 500 characters, in the TextBox. Again, I can't get it to
work.
What am I supposed to do? Does the text fire from a Form? OD I need a
Query
to parse it? How do I run the code?

Thanks,
Ryan---

 

[ryguy7272]

I think I'm trying to force Access to do something that it's not really
designed to do. The Query solution worked fine! The TextBox solution seemed
flawed from the start. I guess, now that I think about it, I was trying to
get the TextBoxes to do something that the Query was already doing for me.
Since a user can use the TransferSpreadsheet method after creating a Query,
and build Forms, reports, and such, off that Query, that really is a much
better way to go.

Thanks for answering my questions guys!! Great learning experience; really
appreciate it!!

Best to both of you,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

No, it's not as simple as that. That code assumes you're passing it a single
value as strText.

Perhaps you should step back and describe what it is you're trying to do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Dale! Works like a Swiss Watch! If a Query was a Swiss Watch.

Doug, where do I feed the data into the parser? I tried the code as is
and
it did nothing. I changed this
strText = "String to split..."

To this:
strText = Table1

My data is stored in Table1. I think I have to point the parser there,
right. How else is it going to know where to look? When I run the macro
now
I get 'Run-time Error 9: Subscript out of Range'. I can't see how to
load
the data into the parser. I'm missing a critical step somewhere...

Sorry for being think here. Trying to learn this stuff so I can do it for
myself next time, and eventually help others, sometime in the future.

Thanks,
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

ryguy,

A standard text box will wrap text automatically, so I'm not entirely
sure
why you would need to do that in a text box. However, I have a similiar
function that I use when I want to display text in a message box, and
want it
to be a certain width.

Copy and Paste this function into a code module (make sure to name the
module something different than the function).

You could use this function anyplace (query, form, report) where you want
to
force the word wrap to occur at (or close to) a specific character
position.
It works great if you are using a non-proportional type font (where each
character takes up the same space) but is not as clean with proportional
type
fonts.

As an example, lets assume you have a field in the recordset of a form.
You
really don't want to edit this field, but you want to display it with a
fixed
(or close) width. In this case, you might set the ControlSource of the
textbox to something like:

ControlSource: =WrapText([FieldName], 50, 3)

The WrapLength parameter defines how wide you want the text to be. The
Variance parameter determines how far prior to the WrapWidth the function
will start looking for a space

Public Function WrapText(ByVal SomeText As Variant, WrapLength As
Integer, _
Optional Variance As Integer = 3) As String

Dim strText As String
Dim intCharPos As Integer, intCRLF As Integer

SomeText = Trim(SomeText)
Do While Len(SomeText & "") > 0
intCRLF = InStr(SomeText, vbCrLf)
If intCRLF > 0 And intCRLF < WrapLength Then
strText = Trim(Left(SomeText, intCRLF + 1))
SomeText = Mid(SomeText, intCRLF + 2)
Else
intCharPos = InStr(fnMin(Len(SomeText & ""), WrapLength -
Variance), SomeText, " ")
If intCharPos = 0 Then
strText = SomeText & ""
SomeText = ""
Else
strText = Trim(Left(SomeText, intCharPos))
SomeText = Mid(SomeText, intCharPos + 1)
End If
End If
If Len(WrapText) = 0 Then
WrapText = strText
ElseIf InStrRev(WrapText, vbCrLf) = Len(WrapText) Then
WrapText = WrapText & strText
Else
WrapText = WrapText & vbCrLf & strText
End If
Loop

End Function

In order to use this, you will also need to copy and paste the function
fnMin below:

Public Function fnMin(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) < myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMin = myVal

End Function

----
HTH
Dale



:

A short time ago I saw an interesting post on this DG. I tried to run
the
code, but no matter what I did, it didn't work (I couldn't get the code
to
fire; nothing happed). Below is the VBA:

Const lngLimit As Long = 500

Dim lngCount As Long
Dim lngSplits As Long
Dim strArray() As String
Dim strText As String

strText = "String to split..."
lngSplits = Len(strText) \ lngLimit

If Len(strText) Mod lngLimit = 0 Then
lngSplits = lngSplits - 1
End If
ReDim strArray(0 To lngSplits)

For lngCount = 0 To lngSplits
strArray(lngCount) = Mid$(strText, lngCount * lngLimit + 1, lngLimit)
Next lngCount

I guess it is supposed to take text in a TextBox and parse it up into
segments of 500 characters, in the TextBox. Again, I can't get it to
work.
What am I supposed to do? Does the text fire from a Form? OD I need a
Query
to parse it? How do I run the code?

Thanks,
Ryan---