Line Break in Access Report

[Deb Roberts]

Hi All

I am trying to produce a "letter" as a report in Access, with a neat & tidy
address block at the top.

I have fields Address1, Address2, Suburb, State & PostCode. Some or all of
these fields may be blank. If I try to set the address block using the
fields individually I get blank lines.

I have several reports with similar problems where blank fields occur.

I would really appreciate some assistance on this - I'm sure it's not so
difficult, but the solution is escaping me at the moment.

Thanks in anticipation !

Cheers

Deb

 

[Tom Wickerath]

Hi Deb,

Set the Can Shrink and Can Grow properties for the textboxes in your report to Yes.


Tom
___________________________________


Hi All

I am trying to produce a "letter" as a report in Access, with a neat & tidy
address block at the top.

I have fields Address1, Address2, Suburb, State & PostCode. Some or all of
these fields may be blank. If I try to set the address block using the
fields individually I get blank lines.

I have several reports with similar problems where blank fields occur.

I would really appreciate some assistance on this - I'm sure it's not so
difficult, but the solution is escaping me at the moment.

Thanks in anticipation !

Cheers

Deb

 

[Douglas J. Steele]

One thing you can try doing is concatenating all of the fields into one,
complete with line breaks, and use the concatenated field as the source for
a single text box that has its CanGrow property set.

Assuming that your "blank" fields actually contain Nulls, not blanks, you
can take advantage of how the + sign works as a concatenation character.
text + null = null + text = null, wherease text & null = null & text = text.
That means you can use something like =[Address1] & (vbCrLf + [Address2]) &
(vbCrLf + ([Suburb] & (", " + [State]) & (" " + [PostCode])) and you
won't have an extra blank line if Address2 doesn't exist.

 

[Deb Roberts]

Thanks Doug
I'll give this a go.

Deb

One thing you can try doing is concatenating all of the fields into one,
complete with line breaks, and use the concatenated field as the source for
a single text box that has its CanGrow property set.

Assuming that your "blank" fields actually contain Nulls, not blanks, you
can take advantage of how the + sign works as a concatenation character.
text + null = null + text = null, wherease text & null = null & text = text.
That means you can use something like =[Address1] & (vbCrLf + [Address2]) &
(vbCrLf + ([Suburb] & (", " + [State]) & (" " + [PostCode])) and you
won't have an extra blank line if Address2 doesn't exist.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi All

I am trying to produce a "letter" as a report in Access, with a neat & tidy
address block at the top.

I have fields Address1, Address2, Suburb, State & PostCode. Some or all of
these fields may be blank. If I try to set the address block using the
fields individually I get blank lines.

I have several reports with similar problems where blank fields occur.

I would really appreciate some assistance on this - I'm sure it's not so
difficult, but the solution is escaping me at the moment.

Thanks in anticipation !

Cheers

Deb

 

[Deb Roberts]

Thanks Tom

 

[Deb Roberts]

Doug

I'm trying to do as you suggested, but am I correct in assuming that this
has to be constructed in VBA, rather than directly on the report?? I'd like
something directly on the report if possible.

Cheers

Deb

One thing you can try doing is concatenating all of the fields into one,
complete with line breaks, and use the concatenated field as the source for
a single text box that has its CanGrow property set.

Assuming that your "blank" fields actually contain Nulls, not blanks, you
can take advantage of how the + sign works as a concatenation character.
text + null = null + text = null, wherease text & null = null & text = text.
That means you can use something like =[Address1] & (vbCrLf + [Address2]) &
(vbCrLf + ([Suburb] & (", " + [State]) & (" " + [PostCode])) and you
won't have an extra blank line if Address2 doesn't exist.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi All

I am trying to produce a "letter" as a report in Access, with a neat & tidy
address block at the top.

I have fields Address1, Address2, Suburb, State & PostCode. Some or all of
these fields may be blank. If I try to set the address block using the
fields individually I get blank lines.

I have several reports with similar problems where blank fields occur.

I would really appreciate some assistance on this - I'm sure it's not so
difficult, but the solution is escaping me at the moment.

Thanks in anticipation !

Cheers

Deb

 

[Douglas J. Steele]

You can add a field to the report, and set its ControlSource to =[Address1]
& (vbCrLf + [Address2]) & (vbCrLf + ([Suburb] & (", " + [State]) & (" " +
[PostCode])) (including the equal sign), or you can add a computed field to
your base query that does that same calculation.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug

I'm trying to do as you suggested, but am I correct in assuming that this
has to be constructed in VBA, rather than directly on the report?? I'd like
something directly on the report if possible.

Cheers

Deb

One thing you can try doing is concatenating all of the fields into one,
complete with line breaks, and use the concatenated field as the source for
a single text box that has its CanGrow property set.

Assuming that your "blank" fields actually contain Nulls, not blanks, you
can take advantage of how the + sign works as a concatenation character.
text + null = null + text = null, wherease text & null = null & text = text.
That means you can use something like =[Address1] & (vbCrLf +

[Address2])

&
(vbCrLf + ([Suburb] & (", " + [State]) & (" " + [PostCode])) and you
won't have an extra blank line if Address2 doesn't exist.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi All

I am trying to produce a "letter" as a report in Access, with a neat & tidy
address block at the top.

I have fields Address1, Address2, Suburb, State & PostCode. Some or

all
of

these fields may be blank. If I try to set the address block using the
fields individually I get blank lines.

I have several reports with similar problems where blank fields occur.

I would really appreciate some assistance on this - I'm sure it's not so
difficult, but the solution is escaping me at the moment.

Thanks in anticipation !

Cheers

Deb

 

[Deb Roberts]

Hi Doug
Thanks for this.

Deb

You can add a field to the report, and set its ControlSource to =[Address1]
& (vbCrLf + [Address2]) & (vbCrLf + ([Suburb] & (", " + [State]) & (" " +
[PostCode])) (including the equal sign), or you can add a computed field to
your base query that does that same calculation.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug

I'm trying to do as you suggested, but am I correct in assuming that this
has to be constructed in VBA, rather than directly on the report?? I'd like
something directly on the report if possible.

Cheers

Deb

One thing you can try doing is concatenating all of the fields into one,
complete with line breaks, and use the concatenated field as the

source
for

a single text box that has its CanGrow property set.

Assuming that your "blank" fields actually contain Nulls, not blanks, you
can take advantage of how the + sign works as a concatenation character.
text + null = null + text = null, wherease text & null = null & text = text.
That means you can use something like =[Address1] & (vbCrLf +

[Address2])

&
(vbCrLf + ([Suburb] & (", " + [State]) & (" " + [PostCode])) and you
won't have an extra blank line if Address2 doesn't exist.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)




Hi All

I am trying to produce a "letter" as a report in Access, with a neat &
tidy
address block at the top.

I have fields Address1, Address2, Suburb, State & PostCode. Some or all
of
these fields may be blank. If I try to set the address block using the
fields individually I get blank lines.

I have several reports with similar problems where blank fields occur.

I would really appreciate some assistance on this - I'm sure it's

not

so