Creating a properly formatted address / Removing blank fields / conditional line breaks

[a.crowley]

It has to be a common problem. You have lots of separate fields in an
Access table representing address data - so how do you get a 'properly
formatted' address in your Report? 'Properly formatted' means that
blank fields are not shown, yet each line of the address starts on a
new line (if required).

The solution is to add a text box to the report, right-mouse click and
select 'Properties'. Under the 'Data' tab of the Text box properties,
drill down on the 'Control Source' item and the Expression Builder
should appear. In here, add the address fields you require interspersed
with conditional formatting. For example:

=IIf(Trim([Cust_Contact])=""," ",[Cust_Contact]) &
IIf(Trim([Cust_Dept])=""," ",Chr(13)+Chr(10)+[Cust_Dept]) &
IIf(Trim([Cust_Company])=""," ",Chr(13)+Chr(10)+[Cust_Company]) &
IIf(Trim([Cust_Add1])=""," ",Chr(13)+Chr(10)+[Cust_Add1]) &
IIf(Trim([Cust_Add2])=""," ",Chr(13)+Chr(10)+[Cust_Add2]) &
IIf(Trim([Cust_Add3])=""," ",Chr(13)+Chr(10)+[Cust_Add3]) &
IIf(Trim([Cust_City])=""," ",Chr(13)+Chr(10)+[Cust_City]) &
IIf(Trim([Cust_County])=""," ",Chr(13)+Chr(10)+[Cust_County]) &
IIf(Trim([Cust_Postcode])="",Chr(13)+Chr(10)+[Cust_Country],Chr(13)+Chr(10)+[Cust_Postcode]
& " "+[Cust_Country])

Note: The + works differently from &. + supports null propagation - if
any component of the expression is null, teh entire expression is null.

Chr(13)+Chr(10) or Chr(13)&Chr(10) adds a line return.