INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Force new line under certain conditions

Force new line under certain conditions

(OP)
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

RE: Force new line under certain conditions

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

RE: Force new line under certain conditions

(OP)
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!

RE: Force new line under certain conditions

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

That column would be as such:

CODE -->

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.

RE: Force new line under certain conditions

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

CODE -->

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

CODE -->

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


RE: Force new line under certain conditions

(OP)
Actually, it seems to me the following will do it.

CODE -->

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.

RE: Force new line under certain conditions

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

RE: Force new line under certain conditions

(OP)
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.

RE: Force new line under certain conditions

Quote (THWatson)

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.

Line 1
   Line 2 

Duane
Hook'D on Access
MS Access MVP

RE: Force new line under certain conditions

(OP)
Duane
MARTIN: Peter &
   SHAMBLIS: Anne 

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!

RE: Force new line under certain conditions

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.

RE: Force new line under certain conditions

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close