Supressing the contents of a field if its empty

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

I have the following in a calculated text box on a report:

=[Contact Name] & Chr(13) & Chr(10) & [Landlords Name] &
Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) &
[Address 2] & Chr(13) & Chr(10) & [Town] & Chr(13) & Chr
(10) & [County] & Chr(13) & Chr(10) & [Postcode]

Which is great. Thank you to Allen Browne.

How can I stop a space being left if a field in the above
is empty. I think I need to put a plus sign in - but
where?
 
Adjusting for Addess2 possibly being empty:

=[Contact Name] & Chr(13) & Chr(10) & [Landlords Name] &
Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) &
[Address 2] + Chr(13) + Chr(10) & [Town] & Chr(13) & Chr
(10) & [County] & Chr(13) & Chr(10) & [Postcode]
 
Sue,

I did some further testing and this works if Address2 is a string (which in
this case it probably is). However, should Address2 be numeric, it fails. In
that case wrap the field with the Str function (not CStr). Str will return a
Null where as CStr will error if the field is Null.

Example:
=[Contact Name] & Chr(13) & Chr(10) & [Landlords Name] &
Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) &
Str([Address 2]) + Chr(13) + Chr(10) & [Town] & Chr(13) & Chr
(10) & [County] & Chr(13) & Chr(10) & [Postcode]


--
Wayne Morgan
MS Access MVP


Wayne Morgan said:
Adjusting for Addess2 possibly being empty:

=[Contact Name] & Chr(13) & Chr(10) & [Landlords Name] &
Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) &
[Address 2] + Chr(13) + Chr(10) & [Town] & Chr(13) & Chr
(10) & [County] & Chr(13) & Chr(10) & [Postcode]

--
Wayne Morgan
MS Access MVP


Sue said:
I have the following in a calculated text box on a report:

=[Contact Name] & Chr(13) & Chr(10) & [Landlords Name] &
Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) &
[Address 2] & Chr(13) & Chr(10) & [Town] & Chr(13) & Chr
(10) & [County] & Chr(13) & Chr(10) & [Postcode]

Which is great. Thank you to Allen Browne.

How can I stop a space being left if a field in the above
is empty. I think I need to put a plus sign in - but
where?
Click to expand...
Click to expand...
 
Thats great. Thanks very much.

Sue
-----Original Message-----
Sue,

I did some further testing and this works if Address2 is a string (which in
this case it probably is). However, should Address2 be numeric, it fails. In
that case wrap the field with the Str function (not CStr). Str will return a
Null where as CStr will error if the field is Null.

Example:
=[Contact Name] & Chr(13) & Chr(10) & [Landlords Name] &
Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) &
Str([Address 2]) + Chr(13) + Chr(10) & [Town] & Chr(13) & Chr
(10) & [County] & Chr(13) & Chr(10) & [Postcode]


--
Wayne Morgan
MS Access MVP


"Wayne Morgan"
Adjusting for Addess2 possibly being empty:

=[Contact Name] & Chr(13) & Chr(10) & [Landlords Name] &
Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) &
[Address 2] + Chr(13) + Chr(10) & [Town] & Chr(13) & Chr
(10) & [County] & Chr(13) & Chr(10) & [Postcode]

--
Wayne Morgan
MS Access MVP


I have the following in a calculated text box on a report:

=[Contact Name] & Chr(13) & Chr(10) & [Landlords Name] &
Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) &
[Address 2] & Chr(13) & Chr(10) & [Town] & Chr(13) & Chr
(10) & [County] & Chr(13) & Chr(10) & [Postcode]

Which is great. Thank you to Allen Browne.

How can I stop a space being left if a field in the above
is empty. I think I need to put a plus sign in - but
where?
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top