Evenly spacing data in a report text box

[webmeister]

I've got a report that has several text boxes of data on it. In one particular text box, with "Can expand" set to yes, the data can be a combination of numeric and alpha, such as:

1, 2A, 3, 4A, 4B, 8, 9, 10, 11A, 11B, 20 and so on.

My question:
How do I prevent a piece of data from "breaking" at the end of a line and continuing onto the next line, within the text box? What happens now is that data comes out like this:

1, 2A, 3, 4
A, 4B, 8, 9
, 10, 11A,
etc......

And what I want ideally is to see:

1, 2A, 3, 4A,
4B, 8, 9, 10,
11A
etc......

Any replies are most welcome, and I look forward to them!!

 

[greaseman]

I have a vba module that, when run from a query, will do what you need. Works like charm. All you have to do is specify your desired length as a parameter in the query.

Here's the module:

Option Compare Database

Option Explicit

Public Function BreakNomen(Nomen As String, NomenWidth As Integer) As String

Dim strItem() As String
Dim intSplit As Integer
Dim intCounter As Integer
Dim intLine As Integer

ReDim strItem(0)
strItem(0) = Nomen

Do While Len(strItem(UBound(strItem))) > NomenWidth

intSplit = InStrRev(Left$(strItem(UBound(strItem)), NomenWidth), ",")

ReDim Preserve strItem(UBound(strItem) + 1)
strItem(UBound(strItem)) = Trim$(Mid(strItem(UBound(strItem) - 1), intSplit))
If Mid$(strItem(UBound(strItem)), 1, 1) = "," Then
strItem(UBound(strItem)) = Mid$(strItem(UBound(strItem)), 2, Len(strItem(UBound(strItem))))
End If
strItem(UBound(strItem) - 1) = RTrim$(Left$(strItem(UBound(strItem) - 1), intSplit - 1))
If intCounter = 0 Then
BreakNomen = strItem(UBound(strItem) - 1)
Else
BreakNomen = BreakNomen & vbCrLf & strItem(UBound(strItem) - 1)
End If
intCounter = intCounter + 1

Loop
If intCounter = 0 Then
BreakNomen = strItem(UBound(strItem))
Else
If Mid$(strItem(UBound(strItem)), 1, 1) = "," Then
strItem(UBound(strItem)) = Mid$(strItem(UBound(strItem)), 2, Len(strItem(UBound(strItem))))
End If
BreakNomen = BreakNomen & vbCrLf & strItem(UBound(strItem))
End If

ExitSub:

Exit Function
End Function

HTH.