Wrapped text for corresponding fields

[George J]

I thought I was finished playing about with the report layouts, but here's another little gem to solve.

One of the fields in the report can have multiple records and the field next to it has corresponding data. This is fine, but gets messy if the text in the first field goes over 1 line.

This looks okay for the report
_________________________
name1 - details1 ¦ cost1
name2 - details2 ¦ cost2
name3 - details3 ¦ cost3
name4 - details4 ¦ cost4

But I have been asked to ensure that the costs line up with the corresponding name and details, which is a problem if more than 1 line is required.
_________________________
name1 - details1 ¦ cost1
name2 - lots of ..¦ cost2
details2 ............¦ cost3
name3 - details3 ¦ cost4
name4 - details4 ¦

I guess the only thing I can really do is check the length of the record against the width of the field and try to work out how many lines are required. If more than 1, then I add a vbCrLf for each additional line to the cost column.

The reports are in Ariel, so would I need to change to Courier? Has anyone done something similar? Other than trial and error, i'm not sure about how to get the desired lengths - or is there some other way of going about this?

Thanks for any advice.

 

[Mihail]

Should not be an issue.
I'm wonder if you really have the second table or is it only what you think that could happen.
Post a pic from your report.

 

[George J]

This is definitely how it looks in the report. I'm not sure what you mean about the second table - The example is the layout of the report. The example shows 2 cells on one row of the report, not a separate row for each 'name'. It is concatenated to group those records to basically one 'cell'.

I can't get to the database today, but I've made a quick example in word.
It shows Barney's costs at £3.50, but because of the text wrapping it is not clear. When I concatenate the data for the Details, the costs are concatenated at the same time, but due to the length of the text in 'details' it may go over 1 or 2 rows. I need to move the corresponding cost to match.

 

[Mihail]

Fortunately you've understand my question.
I have no idea how you have obtained this look. The single way I can imagine is by using a subreport but this seems to not be your case.

For sure we can help you here but (for me) is necessary to take a look to your DB.
Can you upload a 2003 version ? (I use 2007)

 

[George J]

So close with this.

I can identify the width of the control.
I can then find the width of the text string using:

Public Function GetTextLength(ctlReportArea As Control, ByVal strAreaText As String, _
        Optional ByVal Height As Boolean = False)
    Dim lx As Long, ly As Long
    ' Initialize WizHook
    WizHook.Key = 51488399
    ' Populate the variables lx and ly with the width and height of the
    ' string in twips, according to the font settings of the control
    WizHook.TwipsFromFont ctlReportArea.FontName, ctlReportArea.FontSize, ctlReportArea.FontWeight, _
                          ctlReportArea.FontItalic, ctlReportArea.FontUnderline, 0, _
                          strAreaText, 0, lx, ly
    If Not Height Then
        GetTextLength = lx
    Else
        GetTextLength = ly
    End If
End Function

If the result of GetTextLength is less than the width, I put in the string.
Otherwise I put in the string and add x amount of vbCrLf depending on the result of getTextLength/width.

I tried doing this with String(intStrMax, "& vbCrLf") but it doesn't come out right.

If I could just add on the number of carriage returns to the end of the string it would work okay. Any ideas? That line of code is:

Me.CollectiveRate = Me.CollectiveRate & Format(rs!Rate, "Currency") & vbCrLf & String(intStrMax, "& vbCrLf")

 

[George J]

I think that's it - I might be back though.
The string function can give multiple copies of a single character, but CrLf is 2 characters. Replace(String(intStrMax, vbCr), vbCr, vbCrLf) seems to do the trick. I think...

Many thanks for looking at this and to Mihail for replying.