CODEWITHSUNDEEP
ms-accessreport
RyanKDalton
RyanKDalton

Reputation: 1271

MS Access report- line break at character?

I have built a report within MS Access which includes a short textbox that contains web addresses. The textbox has the "CanGrow" option set to "Yes".

Because there is limited horizontal space on the report for this field, and web addresses can be very long, rather than just having the web address spill over where ever the text length forces it to such as:

http://stackoverflow.com/que
stions/ask

I am wondering if there is a way to force the text string to word wrap at the last appropriate character, in this case the "/" character. The result would be something that looks more readable such as:

http://stackoverflow.com/
questions/ask

Can this be done? Any suggestions on how to approach this task?

Upvotes: 0

Views: 1598

Answers (1)

mwolfe02
mwolfe02

Reputation: 24227

The following recursive function will insert a carriage-return/line-feed based on user-defined characters and a max line length. This will work best with a fixed-width font, but with some experimentation should also be acceptable for a variable width font:

Function PrettyBreak(Txt As String, MaxCharsPerLine As Long, _
                     Optional BreakAfterChars As String = ":=-+&?./ ") As String
Dim t As String, i As Integer, Pos As Integer

    If Len(Txt) > MaxCharsPerLine Then
        t = Left(Txt, MaxCharsPerLine)
        For i = MaxCharsPerLine To 1 Step -1
            If InStr(BreakAfterChars, Mid(t, i, 1)) <> 0 Then
                Pos = i
                Exit For
            End If
        Next i
        If Pos = 0 Then
            PrettyBreak = t & vbCrLf & _
                          PrettyBreak(Mid(Txt, MaxCharsPerLine + 1), _
                                      MaxCharsPerLine, BreakAfterChars)
        Else
            PrettyBreak = Left(t, Pos) & vbCrLf & _
                          PrettyBreak(Mid(Txt, Pos + 1), _
                                      MaxCharsPerLine, BreakAfterChars)
        End If
    Else
        PrettyBreak = Txt
    End If
End Function

In use:

?prettybreak("http://stackoverflow.com/questions/5583986/ms-access-report-line-break-at-character", 30)
http://stackoverflow.com/
questions/5583986/ms-access-
report-line-break-at-character

Upvotes: 2

Related Questions