I've created a custom message box for an Access 2007 application that support
HTML messages. In order to keep the message box reasonably attractive when
the message is lengthy I need to insert a "\n" in between words at a
relatively consistent interval. This will cause line breaks to occur at
about the same place on each line. Setting a text interval initially to 25
characters I used the following code, but the Replace command is not only
replacing the space between words, " ", it's replacing everything that comes
before that space as well. Can anyone tell me what I'm doing wrong here?

Ken

Public Function fncInsertStringBreaks(strLongString As String) As String
On Error GoTo PROC_ERROR
Dim strInsertString As String
Dim strTemp As String
Dim strRemainder As String
Dim intLongStringLength As Integer
Dim intInsertInterval As Integer
Dim intInsertCount As Integer
Dim intInsertPosition As Integer
Dim i As Integer

intLongStringLength = Len(strLongString)
strInsertString = "\n"
intInsertInterval = 25
intInsertCount = Int(intLongStringLength / intInsertInterval)
Debug.Print strLongString
For i = 1 To intInsertCount
intInsertPosition = InStr((i * intInsertInterval), strLongString, " ")
Debug.Print intInsertPosition
strTemp = Replace(strLongString, " ", "\n", (intInsertPosition - 1),
1, vbTextCompare)
Debug.Print strTemp
Next i



PROC_EXIT:
Exit Function
PROC_ERROR:
Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Function

Well, Yes and no. The problem is the Replace is loosing the left portion of
the string. It only returns from the starting position, not from the
beginning of the string, but never fear. Here is a replacement that will work
just fine:

Public Function fncInsertStringBreaks(strLongString As String) As String
Const NewLine As String = "\n"
Const OneSpace As String = " "
Dim i As Long
Dim j As Long
Dim strOut As String

On Error GoTo PROC_ERROR

strOut = LongString
i = 25
Do While True
j = InStr(i, strOut, OneSpace)
If j = 0 Then
Exit Do
End If
strOut = Left(strOut, j - 1) & NewLine & Mid(strOut, j + 1)
i = i + 25
Loop
fncInsertStringBreaks = strOut

PROC_EXIT:
Exit Function
PROC_ERROR:
Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
Err.Description, Err.Source)
Resume PROC_EXIT
Resume
End Function

--
Dave Hargis, Microsoft Access MVP


"Ken Warthen" wrote:

> I've created a custom message box for an Access 2007 application that support
> HTML messages. In order to keep the message box reasonably attractive when
> the message is lengthy I need to insert a "\n" in between words at a
> relatively consistent interval. This will cause line breaks to occur at
> about the same place on each line. Setting a text interval initially to 25
> characters I used the following code, but the Replace command is not only
> replacing the space between words, " ", it's replacing everything that comes
> before that space as well. Can anyone tell me what I'm doing wrong here?
>
> Ken
>
> Public Function fncInsertStringBreaks(strLongString As String) As String
> On Error GoTo PROC_ERROR
> Dim strInsertString As String
> Dim strTemp As String
> Dim strRemainder As String
> Dim intLongStringLength As Integer
> Dim intInsertInterval As Integer
> Dim intInsertCount As Integer
> Dim intInsertPosition As Integer
> Dim i As Integer
>
> intLongStringLength = Len(strLongString)
> strInsertString = "\n"
> intInsertInterval = 25
> intInsertCount = Int(intLongStringLength / intInsertInterval)
> Debug.Print strLongString
> For i = 1 To intInsertCount
> intInsertPosition = InStr((i * intInsertInterval), strLongString, " ")
> Debug.Print intInsertPosition
> strTemp = Replace(strLongString, " ", "\n", (intInsertPosition - 1),
> 1, vbTextCompare)
> Debug.Print strTemp
> Next i
>
>
>
> PROC_EXIT:
> Exit Function
> PROC_ERROR:
> Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
> Err.Description, Err.Source)
> Resume PROC_EXIT
> Resume
> End Function
>

Dave,

Thanks for the help. It's annoying that Access help doesn't mention how the
Replace function does not return the whole string. Anyway, thanks again.

Ken

"Klatuu" wrote:

> Well, Yes and no. The problem is the Replace is loosing the left portion of
> the string. It only returns from the starting position, not from the
> beginning of the string, but never fear. Here is a replacement that will work
> just fine:
>
> Public Function fncInsertStringBreaks(strLongString As String) As String
> Const NewLine As String = "\n"
> Const OneSpace As String = " "
> Dim i As Long
> Dim j As Long
> Dim strOut As String
>
> On Error GoTo PROC_ERROR
>
> strOut = LongString
> i = 25
> Do While True
> j = InStr(i, strOut, OneSpace)
> If j = 0 Then
> Exit Do
> End If
> strOut = Left(strOut, j - 1) & NewLine & Mid(strOut, j + 1)
> i = i + 25
> Loop
> fncInsertStringBreaks = strOut
>
> PROC_EXIT:
> Exit Function
> PROC_ERROR:
> Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
> Err.Description, Err.Source)
> Resume PROC_EXIT
> Resume
> End Function
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Ken Warthen" wrote:
>
> > I've created a custom message box for an Access 2007 application that support
> > HTML messages. In order to keep the message box reasonably attractive when
> > the message is lengthy I need to insert a "\n" in between words at a
> > relatively consistent interval. This will cause line breaks to occur at
> > about the same place on each line. Setting a text interval initially to 25
> > characters I used the following code, but the Replace command is not only
> > replacing the space between words, " ", it's replacing everything that comes
> > before that space as well. Can anyone tell me what I'm doing wrong here?
> >
> > Ken
> >
> > Public Function fncInsertStringBreaks(strLongString As String) As String
> > On Error GoTo PROC_ERROR
> > Dim strInsertString As String
> > Dim strTemp As String
> > Dim strRemainder As String
> > Dim intLongStringLength As Integer
> > Dim intInsertInterval As Integer
> > Dim intInsertCount As Integer
> > Dim intInsertPosition As Integer
> > Dim i As Integer
> >
> > intLongStringLength = Len(strLongString)
> > strInsertString = "\n"
> > intInsertInterval = 25
> > intInsertCount = Int(intLongStringLength / intInsertInterval)
> > Debug.Print strLongString
> > For i = 1 To intInsertCount
> > intInsertPosition = InStr((i * intInsertInterval), strLongString, " ")
> > Debug.Print intInsertPosition
> > strTemp = Replace(strLongString, " ", "\n", (intInsertPosition - 1),
> > 1, vbTextCompare)
> > Debug.Print strTemp
> > Next i
> >
> >
> >
> > PROC_EXIT:
> > Exit Function
> > PROC_ERROR:
> > Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
> > Err.Description, Err.Source)
> > Resume PROC_EXIT
> > Resume
> > End Function
> >

Yeah, Help can sometimes be frustrating. Sort of like:
Instructions for Using Parachute:

Put Arms through harness arm straps.
Put legs throught harness leg straps.
Close harness buckle.
Jump out of airplane.

Then it assumes you know you should pull the rip cord

--
Dave Hargis, Microsoft Access MVP


"Ken Warthen" wrote:

> Dave,
>
> Thanks for the help. It's annoying that Access help doesn't mention how the
> Replace function does not return the whole string. Anyway, thanks again.
>
> Ken
>
> "Klatuu" wrote:
>
> > Well, Yes and no. The problem is the Replace is loosing the left portion of
> > the string. It only returns from the starting position, not from the
> > beginning of the string, but never fear. Here is a replacement that will work
> > just fine:
> >
> > Public Function fncInsertStringBreaks(strLongString As String) As String
> > Const NewLine As String = "\n"
> > Const OneSpace As String = " "
> > Dim i As Long
> > Dim j As Long
> > Dim strOut As String
> >
> > On Error GoTo PROC_ERROR
> >
> > strOut = LongString
> > i = 25
> > Do While True
> > j = InStr(i, strOut, OneSpace)
> > If j = 0 Then
> > Exit Do
> > End If
> > strOut = Left(strOut, j - 1) & NewLine & Mid(strOut, j + 1)
> > i = i + 25
> > Loop
> > fncInsertStringBreaks = strOut
> >
> > PROC_EXIT:
> > Exit Function
> > PROC_ERROR:
> > Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
> > Err.Description, Err.Source)
> > Resume PROC_EXIT
> > Resume
> > End Function
> >
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Ken Warthen" wrote:
> >
> > > I've created a custom message box for an Access 2007 application that support
> > > HTML messages. In order to keep the message box reasonably attractive when
> > > the message is lengthy I need to insert a "\n" in between words at a
> > > relatively consistent interval. This will cause line breaks to occur at
> > > about the same place on each line. Setting a text interval initially to 25
> > > characters I used the following code, but the Replace command is not only
> > > replacing the space between words, " ", it's replacing everything that comes
> > > before that space as well. Can anyone tell me what I'm doing wrong here?
> > >
> > > Ken
> > >
> > > Public Function fncInsertStringBreaks(strLongString As String) As String
> > > On Error GoTo PROC_ERROR
> > > Dim strInsertString As String
> > > Dim strTemp As String
> > > Dim strRemainder As String
> > > Dim intLongStringLength As Integer
> > > Dim intInsertInterval As Integer
> > > Dim intInsertCount As Integer
> > > Dim intInsertPosition As Integer
> > > Dim i As Integer
> > >
> > > intLongStringLength = Len(strLongString)
> > > strInsertString = "\n"
> > > intInsertInterval = 25
> > > intInsertCount = Int(intLongStringLength / intInsertInterval)
> > > Debug.Print strLongString
> > > For i = 1 To intInsertCount
> > > intInsertPosition = InStr((i * intInsertInterval), strLongString, " ")
> > > Debug.Print intInsertPosition
> > > strTemp = Replace(strLongString, " ", "\n", (intInsertPosition - 1),
> > > 1, vbTextCompare)
> > > Debug.Print strTemp
> > > Next i
> > >
> > >
> > >
> > > PROC_EXIT:
> > > Exit Function
> > > PROC_ERROR:
> > > Call ShowError("modDSI", "fncInsertStringBreaks", Err.Number,
> > > Err.Description, Err.Source)
> > > Resume PROC_EXIT
> > > Resume
> > > End Function
> > >