Labels

R

Roger Bell

I have designed a label fomat using a Text Box as follows:

=Trim([title for pg] & " " & [lname]) & Chr(13) & Chr(10) & Trim([unit/flat]
& " " & [unit/flat no] & Chr(13) & Chr(10) & Trim([street number] & "" &
[street prefix] & " " & [street name]) & Chr(13) & Chr(10) & Trim([suburb] &
" " & [state] & " " & [pcode]))

The labels are OK except when there is no Unit/Flat in the address, a Blank
line is created. I have set Properties to Can Shrink. Is there any way that I
can remove the Blank Line when there is no Unit/Flat in the Address?

Thanks for any help
 
A

Allen Browne

Roger, try:

=Trim([title for pg] & " " & [lname]) + Chr(13) + Chr(10) &
Trim([unit/flat] & " " & [unit/flat no]) + Chr(13) + Chr(10) &
Trim([street number] & " " & [street prefix] & " " & [street name]) +
Chr(13) + Chr(10) & Trim([suburb] & " " & [state] & " " & [pcode])

Explanation:
There is a subtle difference between the 2 concatenation operators in
Access:
"A" & Null = "A"
"A" + Null = Null
 
J

John Spencer

One small suggestion -

Trim([unit/flat] & " " & [unit/flat no]) will always return a string even
if both fields are null.

Trim([unit/flat] & (" " + [unit/flat no]) ) will return null if both fields
are null, otherwise it will return the desired string.

... & Trim([unit/flat] & (" " + [unit/flat no])) + Chr(13) + Chr(10) & ...


Allen Browne said:
Roger, try:

=Trim([title for pg] & " " & [lname]) + Chr(13) + Chr(10) &
Trim([unit/flat] & " " & [unit/flat no]) + Chr(13) + Chr(10) &
Trim([street number] & " " & [street prefix] & " " & [street name]) +
Chr(13) + Chr(10) & Trim([suburb] & " " & [state] & " " & [pcode])

Explanation:
There is a subtle difference between the 2 concatenation operators in
Access:
"A" & Null = "A"
"A" + Null = Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Roger Bell said:
I have designed a label fomat using a Text Box as follows:

=Trim([title for pg] & " " & [lname]) & Chr(13) & Chr(10) &
Trim([unit/flat]
& " " & [unit/flat no] & Chr(13) & Chr(10) & Trim([street number] & "" &
[street prefix] & " " & [street name]) & Chr(13) & Chr(10) &
Trim([suburb] &
" " & [state] & " " & [pcode]))

The labels are OK except when there is no Unit/Flat in the address, a
Blank
line is created. I have set Properties to Can Shrink. Is there any way
that I
can remove the Blank Line when there is no Unit/Flat in the Address?

Thanks for any help
 
A

Allen Browne

Thanks, John.

You're right, of course.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

John Spencer said:
One small suggestion -

Trim([unit/flat] & " " & [unit/flat no]) will always return a string even
if both fields are null.

Trim([unit/flat] & (" " + [unit/flat no]) ) will return null if both
fields are null, otherwise it will return the desired string.

... & Trim([unit/flat] & (" " + [unit/flat no])) + Chr(13) + Chr(10) &
...


Allen Browne said:
Roger, try:

=Trim([title for pg] & " " & [lname]) + Chr(13) + Chr(10) &
Trim([unit/flat] & " " & [unit/flat no]) + Chr(13) + Chr(10) &
Trim([street number] & " " & [street prefix] & " " & [street name]) +
Chr(13) + Chr(10) & Trim([suburb] & " " & [state] & " " & [pcode])

Explanation:
There is a subtle difference between the 2 concatenation operators in
Access:
"A" & Null = "A"
"A" + Null = Null

Roger Bell said:
I have designed a label fomat using a Text Box as follows:

=Trim([title for pg] & " " & [lname]) & Chr(13) & Chr(10) &
Trim([unit/flat]
& " " & [unit/flat no] & Chr(13) & Chr(10) & Trim([street number] & "" &
[street prefix] & " " & [street name]) & Chr(13) & Chr(10) &
Trim([suburb] &
" " & [state] & " " & [pcode]))

The labels are OK except when there is no Unit/Flat in the address, a
Blank
line is created. I have set Properties to Can Shrink. Is there any way
that I
can remove the Blank Line when there is no Unit/Flat in the Address?

Thanks for any help
 
R

Roger Bell

Thank You both very much for your continuous expertise

Allen Browne said:
Thanks, John.

You're right, of course.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

John Spencer said:
One small suggestion -

Trim([unit/flat] & " " & [unit/flat no]) will always return a string even
if both fields are null.

Trim([unit/flat] & (" " + [unit/flat no]) ) will return null if both
fields are null, otherwise it will return the desired string.

... & Trim([unit/flat] & (" " + [unit/flat no])) + Chr(13) + Chr(10) &
...


Allen Browne said:
Roger, try:

=Trim([title for pg] & " " & [lname]) + Chr(13) + Chr(10) &
Trim([unit/flat] & " " & [unit/flat no]) + Chr(13) + Chr(10) &
Trim([street number] & " " & [street prefix] & " " & [street name]) +
Chr(13) + Chr(10) & Trim([suburb] & " " & [state] & " " & [pcode])

Explanation:
There is a subtle difference between the 2 concatenation operators in
Access:
"A" & Null = "A"
"A" + Null = Null

I have designed a label fomat using a Text Box as follows:

=Trim([title for pg] & " " & [lname]) & Chr(13) & Chr(10) &
Trim([unit/flat]
& " " & [unit/flat no] & Chr(13) & Chr(10) & Trim([street number] & "" &
[street prefix] & " " & [street name]) & Chr(13) & Chr(10) &
Trim([suburb] &
" " & [state] & " " & [pcode]))

The labels are OK except when there is no Unit/Flat in the address, a
Blank
line is created. I have set Properties to Can Shrink. Is there any way
that I
can remove the Blank Line when there is no Unit/Flat in the Address?

Thanks for any help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Address Envelope with Trim 1
Mailing Address 3
Mailing address 1
Mailing Address 5
Address 1
Mailing Labels 3
mailing labels - square box at end of first line in report 8
Text Box in a Report 1

Top