Report Fields that Auto Shrink/Grow

#1 (**permalink**) 28-Jul-2006, 08:12 AM

Hello everyone,

When creating a report of mailing labels, is it possible to make each
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10383-report-fields-that-auto-shrink-grow.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
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.

--
Viken K.
http://www.vikenk.com

#2 (**permalink**) 28-Jul-2006, 08:12 AM

Instead of having 1 field for first name, and another for last name,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
have 1 field for Name, and have it be an expression, that combines the
2 together.

#3 (**permalink**) 28-Jul-2006, 08:12 AM

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

--
Viken K.
http://www.vikenk.com
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383

#4 (**permalink**) 28-Jul-2006, 08:12 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
on the same line.

"vikenk@aol.com" wrote:

> Can you exand on how to do that? I'm not really knowledgeable about
> writing expressions. Thanks :>)
>
> --
> Viken K.
> http://www.vikenk.com
>
>

#5 (**permalink**) 28-Jul-2006, 08:12 AM

xRoachx wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
> 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]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383

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?

--
Viken K.
http://www.vikenk.com

#6 (**permalink**) 28-Jul-2006, 08:12 AM

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.

"vikenk@aol.com" wrote:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
>
> xRoachx wrote:
> > Hi Viken -- To do this, open the report in design view and add an unbound
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
> > 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?
>
> --
> Viken K.
> http://www.vikenk.com
>
>

#7 (**permalink**) 28-Jul-2006, 08:12 AM

xRoachx wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383

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 :>)

--
Viken K.
http://www.vikenk.com

#8 (**permalink**) 28-Jul-2006, 08:12 AM

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

"vikenk@aol.com" wrote:

>
> xRoachx wrote:
> > Ah, I assumed you would have both names...
>
> Thanks for the info. I think you misunderstood me, though :>) Your
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
> original solution of using =[First Name] & " " & [Last Name] works
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10383
> 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 :>)
>
> --
> Viken K.
> http://www.vikenk.com
>
>