replace cut-off text with other text in access report

[jsqsh]

Hello guys, this is my first thread. I have been trying to figure out how to do this for a while now, and got to the end of my rope and figured it was time to let the question out into the world.

I have a report in access that displays certain information from a database, in a limited amount of space. So what I have to deal with is certain things getting cut off when they're displayed. What I would like to be able to do is, rather than having a name "John James Doe" Cut off to "John James D", I would like to trim off from that last space in the manner "John James" or "John James..." with the three dots letting those reading the reports know that the field was cut off. Is anyone able to suggest how to do this?


That tiny picture is an example, where "J & C CAMPBELL INVESTMENT COMPANY" Was cut off, and Hood is the start of a new field. Would like it to say "J & C CAMPBELL INVESTMENT..." or such.

Any help is appreciated, thanks guys. These are just standard text boxes by the way.

[ArviLaanemets]

Simply have in table where you register those names a field for short name, and in your report use this short name instead of full one.

[davegri]

Private Sub Form_Current()
    If Len(Text10) > 12 Then
        Text10 = Left(Text10, 9) & "..."
    End If
End Sub

Determine the length that the textbox allows and adjust the above to fit.

[June7]

Could do IIf() in query or textbox and no VBA needed.

[orange]

jsqsh,

What do you do with names such as
Oscar de la Hoya?
John Paul Jones BSc.?
Marco Dal Santo?

[June7]

jsqsh,

The point orange is driving at is doesn't matter if the last word is removed, some values not going to fit the restricted space and will appear truncated. I think davegri's suggestion is a reasonable compromise. Limit display to a set number of characters regardless of where the cutoff is and then append ellipsis "..."

[orange]

I agree June.

jsqsh,
Who will be reviewing the report? Do truncated names create an issue?
Do the names have meaning to the users?

[jsqsh]

Simply have in table where you register those names a field for short name, and in your report use this short name instead of full one.

Good suggestion, that would definitely work and I don't know why I didn't think of that.

[jsqsh]

orange,

those viewing the report will be varied, but the data it refers to will have a unique key provided for each entry, so having a truncated name shouldn't be an issue. I just want it to be obvious to the reader that they aren't looking at the full name, and I figure the ellipsis would be a good way to do that.