Report Fields that Auto Shrink/Grow

[vikenk]

Hello everyone,

When creating a report of mailing labels, is it possible to make each
field grow and shrink accordingly so there aren't awkward spaces
between first names and last names? I tried going into the Properties
in Design mode and setting "Can Grow" and "Can Shrink" to YES, but that
doesn't seem to do the trick. See this sample layout:

First Name Last Name
Address 1
Address 2
City State ZIP

If I size the First Name field to accomodate long names, there's an big
gap between shorter first names and the last name. If there's no first
name at all, there's a big space before the last name. I'd like the
field to shrink for short names, grow for long names and disappear for
no names. If there's no Address 2, I'd like the City, State, ZIP fields
to shift up accordingly. Can these be done?

Thanks a lot.

 

[kerry_ja]

Instead of having 1 field for first name, and another for last name,
have 1 field for Name, and have it be an expression, that combines the
2 together.

 

[vikenk]

Can you exand on how to do that? I'm not really knowledgeable about
writing expressions. Thanks :>)

 

[Guest]

Hi Viken -- To do this, open the report in design view and add an unbound
text box to the report. Set the control source to:

=[First Name] & " " & [Last Name]

Where the First Name and Last Name are the names of your fields. You could
similarly concactenate the Address1 and Address2 fields but they would appear

 

[vikenk]

Hi Viken -- To do this, open the report in design view and add an unbound
text box to the report. Set the control source to:

=[First Name] & " " & [Last Name]

Thanks! It worked great! I had tried plunking though it. I used the
Expression Builder to make this:

=[First Name]+[Last Name]

It worked, but only when there was BOTH a first name and a last name.
If one of the fields were empty, the whole expression was blank.

You could
similarly concactenate the Address1 and Address2 fields but they would appear
on the same line.

This may be a dumb question, but I suppose there wouldn't be a way to
add a line break to the expression?

 

[Guest]

Ah, I assumed you would have both names...

To take care of the blanks, use the Nz function:

=Nz([First Name], " ") & " " & Nz([Last Name], " ")

Regarding the line break, this can be added by using the ASC II value but
you still have the same problem if address2 is blank.

Hi Viken -- To do this, open the report in design view and add an unbound
text box to the report. Set the control source to:

=[First Name] & " " & [Last Name]

Thanks! It worked great! I had tried plunking though it. I used the
Expression Builder to make this:

=[First Name]+[Last Name]

It worked, but only when there was BOTH a first name and a last name.
If one of the fields were empty, the whole expression was blank.

You could
similarly concactenate the Address1 and Address2 fields but they would appear
on the same line.

This may be a dumb question, but I suppose there wouldn't be a way to
add a line break to the expression?

 

[vikenk]

Ah, I assumed you would have both names...

Thanks for the info. I think you misunderstood me, though :>) Your
original solution of using =[First Name] & " " & [Last Name] works
perfectly, even when one of the fields is blank.

It was *my* ealier attempt at building the expression =[First
Name]+[Last Name] that was the one that failed when one of the fields
was blank.

I have found myself suddenly doing a buch of database "work" (it's more
like volunteer work for friends) after not having done any for a long
time. You'll be seeing me on this board a few more times :>)

 

[Guest]

No problem, I'm glad I could help. These boards are a very good resource for
answers and solutions...

Ah, I assumed you would have both names...

Thanks for the info. I think you misunderstood me, though :>) Your
original solution of using =[First Name] & " " & [Last Name] works
perfectly, even when one of the fields is blank.

It was *my* ealier attempt at building the expression =[First
Name]+[Last Name] that was the one that failed when one of the fields
was blank.

I have found myself suddenly doing a buch of database "work" (it's more
like volunteer work for friends) after not having done any for a long
time. You'll be seeing me on this board a few more times :>)