Line Break in Access Report

  • Thread starter Thread starter Deb Roberts
  • Start date Start date
D

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
 
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
 
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.
 
Thanks Doug
I'll give this a go.

Deb

Douglas J. Steele said:
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)



Deb Roberts said:
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
Click to expand...
Click to expand...
 
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

Douglas J. Steele said:
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)



Deb Roberts said:
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
Click to expand...
Click to expand...
 
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)



Deb Roberts said:
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

Douglas J. Steele said:
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 +
Click to expand...
[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)



Deb Roberts said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Hi Doug
Thanks for this.

Deb

Douglas J. Steele said:
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)



Deb Roberts said:
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

Douglas J. Steele said:
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
Click to expand...
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 +
Click to expand...
[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
Click to expand...
Click to expand...
not
so
Click to expand...
Click to expand...
 
Back
Top