Suppressing blank lines on printout.

[Nodge]

First, thanks for all the help with my first question. What a knowledgeable
bunch you are!

Back to printing out my names and addresses. I dont want blank lines printed
out in the middle of my address so I've set each text block to shrink =
Yes. This works fine. Trouble now is I want the address printed in a
rectangular window (pref with rounded corners but that may be asking too
much). I draw a rectangle around my address blocks and now when I print my
addresses I get blank lines appearing again.

HELP!

Nodge

Incidentally, Up til now I've been using a prog called Buttonfile (a very
early Windows database). It was written by Jim Button who wrote PC-File, one
of the first ever shareware progs. Buttonfile has done everything I need
until just recently when I needed to include some graphics which it doesn't
handle, hence why I'm struggling with Access. If anyone can recommend a
simple flatfile database that's user friendly and allows graphics in reports
then I'd give it a go.

Thanks.

 

[Guest]

Any graphical controls alongside a txt box will prevent it shrinking, as
you've found. If the rectangle is to be of a fixed size, i.e. does not
shrink with the text it encloses, then you can place the rectangle in the
report and put a single text box at the top of it. The text box's CanGrow
property should be True this time. For the text box's ControlSource use a
computed column in the query which concatenates the values from the various
columns making up the address. To suppress the blank lines the trick is to
use the + operator rather than the usual ampersand concatenation operator.
This makes use of the fact that Nulls propagate, so anything + Null = Null.
This can be used to suppress the carriage return/line feed after a blank
(Null) line.

The easiest way to do this is to put a little function like that below in a
standard module in the database. This can then be called from anywhere in
the database to concatenate the values. By using a parameter array as the
function's argument any number of address lines can be passed into it:

Public Function GetFullAddress(ParamArray varAddressLines() As Variant) As
String

Dim strAddress As String
Dim var As Variant

For Each var In varAddressLines
strAddress = strAddress & (vbNewLine + var)
Next var

' remove leading carriage return/line feed
GetFullAddress = Mid$(strAddress, 3)

End Function

So it might be called by entering something like this in the field row of a
blank column in query design view:

FullAddress: GetFullAddress(([FirstName] + " ") & [LastName],
[AddressLine1], [AddressLine2], [City])

which would give you something like:

Tony Blair
10 Downing Street
London

where the AddressLine2 field is Null. Note how the + operator is also used
when passing the arguments into the function to suppress the space between
FirstName and LastName in the first line if the FirstName is Null.

In a report based on the query the text box's ControlSource would be
FullAddress.

You could of course alternatively call the function in the report rather
than a query by making the ControlSource of the text box:

= GetFullAddress(([FirstName] + " ") & [LastName], [AddressLine1],
[AddressLine2], [City])

As for a flat file database Microsoft Works includes a simple one, which
might be worth a look.

Ken Sheridan
Stafford, England

 

[Nodge]

Ken, many thanks for your reply, but as I'm a complete noob I'm afraid most
of it went way over my head. I'm beginning to think that if I can't draw a
simple box without resorting to parameter arrays, functions and the like
then maybe Access is not the software I should be using. (It was all so easy
in Buttonfile.)
Think I need something more wysiwyg. I'm a typesetter/designer by profession
and I'm used to just designing on screen without resorting to all the maths.

Nodge

Any graphical controls alongside a txt box will prevent it shrinking, as
you've found. If the rectangle is to be of a fixed size, i.e. does not
shrink with the text it encloses, then you can place the rectangle in the
report and put a single text box at the top of it. The text box's CanGrow
property should be True this time. For the text box's ControlSource use a
computed column in the query which concatenates the values from the various
columns making up the address. To suppress the blank lines the trick is to
use the + operator rather than the usual ampersand concatenation operator.
This makes use of the fact that Nulls propagate, so anything + Null = Null.
This can be used to suppress the carriage return/line feed after a blank
(Null) line.

The easiest way to do this is to put a little function like that below in a
standard module in the database. This can then be called from anywhere in
the database to concatenate the values. By using a parameter array as the
function's argument any number of address lines can be passed into it:

Public Function GetFullAddress(ParamArray varAddressLines() As Variant) As
String

Dim strAddress As String
Dim var As Variant

For Each var In varAddressLines
strAddress = strAddress & (vbNewLine + var)
Next var

' remove leading carriage return/line feed
GetFullAddress = Mid$(strAddress, 3)

End Function

So it might be called by entering something like this in the field row of a
blank column in query design view:

FullAddress: GetFullAddress(([FirstName] + " ") & [LastName],
[AddressLine1], [AddressLine2], [City])

which would give you something like:

Tony Blair
10 Downing Street
London

where the AddressLine2 field is Null. Note how the + operator is also used
when passing the arguments into the function to suppress the space between
FirstName and LastName in the first line if the FirstName is Null.

In a report based on the query the text box's ControlSource would be
FullAddress.

You could of course alternatively call the function in the report rather
than a query by making the ControlSource of the text box:

= GetFullAddress(([FirstName] + " ") & [LastName], [AddressLine1],
[AddressLine2], [City])

As for a flat file database Microsoft Works includes a simple one, which
might be worth a look.

Ken Sheridan
Stafford, England

First, thanks for all the help with my first question. What a knowledgeable
bunch you are!

Back to printing out my names and addresses. I dont want blank lines printed
out in the middle of my address so I've set each text block to shrink =
Yes. This works fine. Trouble now is I want the address printed in a
rectangular window (pref with rounded corners but that may be asking too
much). I draw a rectangle around my address blocks and now when I print my
addresses I get blank lines appearing again.

HELP!

Nodge

Incidentally, Up til now I've been using a prog called Buttonfile (a very
early Windows database). It was written by Jim Button who wrote PC-File, one
of the first ever shareware progs. Buttonfile has done everything I need
until just recently when I needed to include some graphics which it doesn't
handle, hence why I'm struggling with Access. If anyone can recommend a
simple flatfile database that's user friendly and allows graphics in reports
then I'd give it a go.

Thanks.