Labels

[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

 

[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

 

[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) & ...

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.

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

 

[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.

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) &
...

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

 

[Roger Bell]

Thank You both very much for your continuous expertise

Thanks, John.

You're right, of course.

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

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

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) &
...

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