Force new line under certain conditions

[THWatson]

A report in a church database produces pages of records of parishioners to send to a company that will print a Photo Directory for the church.

The company's printing requirements are pretty specific. 8 1/2 x 11 size pages, four columns across the page, vertically then horizontally. Arial Narrow 9 point font.

Here's how an entry would look:
SMITH: Jack and Jane
1002 Circle AVE
Anytown, ON N1Z 3Y5

or this, is the surname of the second person is not the same as the surname of the first person:
SMITH: Jack and JONES: Jane
1002 Circle AVE
Anytown, ON N1Z 3Y5

The text box in the report that produces the top line is
=Format([LastName],">") & ": " & [FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " "))," & " & Format([LastName2],">") & ": " & [FirstName2])

This works fine except in a few instances where the names are too long for the column width, so part of it gets flipped to another line. For example...
ZANDOVER: Abraham and HOOVER:
Gwendolyn

My sense is it would look better if that were to present like so:
ZANDOVER: Abraham and
HOOVER: Gwendolyn

Is there a way to accomplish that? Or is it simply too much trouble for perhaps 10 names out of the whole bunch?

Thanks.

Tom

 

[dhookom]

I think you might be able to replace a standard space with a hard space. I googled the topic and found this thread which I had forgotten.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
I see what you mean. Trouble is I don't want a new line forced in every instance. I only want it in certain instances.

In the great majority of cases entries end up looking like this:
ALDAS: Carolynne & John
BARLERT: Glen & Carolyn
LAMUX: Alex & ANDERSON: Gayle

Note that last entry. The wife kept her surname upon marriage. This entry is short enough that it doesn't exceed the column width.

The fields are: LastName, LastName2 (LastName2 only comes into play if the two people in the home have different surnames).

But, then there are these:
example one
BAMENDREGT: Daniel & DANCE:
Anne

example two
ZAMOYC: Anthony & GRANT:
Shirley

My sense is it would look nicer if those entries ended up looking like so:
BAMENDREGT: Daniel &
DANCE: Anne

ZAMOYC: Anthony &
GRANT: Shirley

Here's one that breaks like that, because of the length of the first surname:
example three
COXBROUGH: Heather &
GREGORY: Eric

So the question is: what could be placed in the code so that if LastName2 exists it will be sent to a second line.
That doesn't need to happen in, example three above (but could if necessary) but needs to happen in examples one and two.

IDEA: I could, I suppose force the LaseName2 to a second line in all instances.

Where I started thinking was some kind of test for when LastName2 exists but the names exceed the column width.

On the other hand, maybe I'm just trying to be too much of a perfectionist here...and therefore worrying needlessly about this...and maybe somebody else would look at that and say "no problem at all, leave it alone."
I would respect your guidance.

Thanks!

 

[THWatson]

I could, I suppose, put another column in the query upon which this report is based.

That column would be as such:

IIf([LastName2],Format([LastName],">") & ": " & [FirstName] & " & " & "
" & Format([LastName2],">") & ": " & [FirstName2],[LastName] & ": " & [FirstName] & " & " & [FirstName2])

That would force ALL circumstances where a LastName2 exists to drop that LastName2 and FirstName2 to a second line.

 

[THWatson]

The question now becomes: How to meld the following two pieces of code in the most efficient manner.

=Format([LastName],">") & ": " & [FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " "))," & " & Format([LastName2],">") & ": " & [FirstName2])

IIf([LastName2],Format([LastName],">") & ": " & [FirstName] & " & " & "
" & Format([LastName2],">") & ": " & [FirstName2],[LastName] & ": " & [FirstName] & " & " & [FirstName2])

 

[THWatson]

Actually, it seems to me the following will do it.

Format([LastName],">") & ": " & [FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " "))," & " & "
   " & Format([LastName2],">") & ": " & [FirstName2])

Since the code line requires scrolling, I'll put it here in bold print.
Format([LastName],">") & ": " & [FirstName] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " "))," & " & "
" & Format([LastName2],">") & ": " & [FirstName2])

Unless there's a more elegant way to do it.

 

[dhookom]

I thought all you need to do is place a hard space between lastname2 and firstname2 so they didn't break apart.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
I tried that. Couldn't make it work with Chr(160).

Maybe putting " at the end of one line, and then Ctrl-Enter and then three spaces followed by the close quote " does the same.

In any event, the code I posted above does two things. It forces the new line when both LastName2 and FirstName2 exist, and also indents by three space.

So an entry will show as
MARTIN: Peter &
SHAMBLIS: Anne (this line is pushed in three spaces, but I can't get those spaces to show here, and you will get the idea).

Thanks for all your help.

 

[dhookom]

but I can't get those spaces to show here

You seem to be fairly comfortable with using TGML. You might want to try the Pre tag.

[pre]Line 1
Line 2[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
MARTIN: Peter &
[pre] SHAMBLIS: Anne[/pre]

I didn't know what that meant. I had to fool around a bit with it but I see now how it works. I will know in the future.

THANKS!

 

[SkipVought]

If this were a recurring business requirement, I'd try to go for a totally coded solution.

In this case, the code gets you 90%, and I'd do the remainder manually to achieve a visually pleasing product.

 

[THWatson]

Thanks for the idea.

This is a one-shot Photo Directory report. The church only gets those done about every 6 years or so.

All the best.