Problem with the Label Wizzard Report 1

[Cosette]

Good morning all,

I have a label for which the name field is programmed. Depending on the last name and address, I have two different type of text being printed:

Sarah and Mark Jones

OR

Sarah Smith and Mark Jones

My problem is that when the names are too long, it doesn't create the full second name on the second line. What it prints is the following:

Dr. Sarah Goodshall and Rev. Jonathan
Gordon

instead of:
Dr. Sarah Goodshall and
Rev. Jonathan Gordon

I have included the code below. The field name is txtName I really don't know programatically how to create the new line after the and. Thanks all for your help.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)[/color green]

If Me.LastName2 = Me.LastName1 Then
Me.txtName = Trim([Title1] & " " & [Name1] & " and " & [Title2] & " " & [Name2] & " " & [LastName1])

ElseIf Me.LastName2 <> Me.LastName1 Then
Me.txtName = Trim([Title1] & " " & [Name1] & " " & [LastName1] & " and " & [Title2] & " " & [Name2] & " " & [LastName2])

ElseIf IsNull([LastName2]) Then
Me.txtName = Trim([Title1] & " " & [Name1] & " " & [LastName1])

End If
End Sub

 

[THWatson]

Cosette
I don't see anything in your code that would force the full second name to a new line.

So I think you will have to decide whether you want that to happen all the time...in other words, if there is a second name to always break that one to a new line. To do that include in your code a break to a new line if the last names are not the same.

Or, better still, can you not make the text box wider so that the names always appear on the same line?

Tom

 

[Cosette]

Tom,

I work off the label wizzard, so I don't want to expand the box until it prints outside teh label.

I thought that there was a way, based on length to curtail an entire new name but I guess there isn't. Do you know how to go about creating the second line without having a blank line if the names fit on one full line?

Thanks Tom

Cosette

 

[THWatson]

Cosette
Well, even if you set things up by using the label wizard you can still make the first line text box wider. However, you may have a reason you don't want it wider. Or, another possibility would be to decrease the font size slightly, so that all names fit on one line in all circumstances.

There may be code available that determines the length of the line. Personally, I don't know it. The problem is that characters don't take up the same amount of space - some letters are wider than others. So it's a bit convoluted to try and determine the line width upon formatting.

It's easy to force a second line when the last names are not the same, but that may not be what you want.

Tom

 

[sxschech]

What about this? Determine the length you want to cut off at and then do a vbcrlf as in example below:

=IIf(Len(Field1)>25,Replace(Field1,"and ","and" & Chr(13)+Chr(10)),Field1)

This will turn:
Dr. Sarah Goodshall and Rev. Jonathan Gordon

Into:
Dr. Sarah Goodshall and
Rev. Jonathan Gordon

You may need to play around with the Len value to have it cut off at the right number of characters.

 

[THWatson]

Yes, I agree. That might work.

Good catch, sxschech! Worth a star.

Tom

 

[Cosette]

Thanks sxschech, it did work.

Cosette