Report question - how to "ignore" blank fields?

[Nozza]

I have a database with some address data. The individual lines for
each address are stored in the customer table.

I want to create a report with a customer address at the top of each
page where the post code is immediately underneath the last line of
the address with data.

The customer record holds (amongst other things)

AddressLine1
AddressLine2
AddressLine3
AddressLine4
Postcode

Some addresses are made of two only two address lines and a postcode
such as

10 Downing Street
London
SW1A 2AA

Others have three or four lines and a postcode.

The four lines is no problem.

But I am having problems formatting the 2 and 3 address lines and a
postcode

For the address above I would see

10 Downing Street
London
<blank line>
<blank line>
SW1A 2AA

Short of going through first normal form to remove the repeating
address lines in the address and creating a separate address table,
how can I get a report to display what I want.

With many thanks

Noz

 

[John Spencer]

One method is to use a calculated field in the underlying query.

Assumption by blank, you mean the field is null.

The following would be all on one line in your query

Field: FullAddress:
AddressLine1 &
(Chr(13) + Chr(10) + AddressLine2) &
(Chr(13) + Chr(10) + AddressLine3) &
(Chr(13) + Chr(10) + AddressLine4) &
(Chr(13) + Chr(10) + PostCode)

This takes advantage of the fact that Access concatenates data together
differently with the + and the & operators.

Null + "Something" will return null
Null & "Something" will return "Something"

You can use the same type of formula directly in the report as the source of a control.

 

[Nozza]

One method is to use a calculated field in the underlying query.

Assumption by blank, you mean the field is null.

The following would be all on one line in your query

Field: FullAddress:
AddressLine1 &
(Chr(13) + Chr(10) + AddressLine2) &
(Chr(13) + Chr(10) + AddressLine3) &
(Chr(13) + Chr(10) + AddressLine4) &
(Chr(13) + Chr(10) + PostCode)

This takes advantage of the fact that Access concatenates data together
differently with the + and the & operators.

Null + "Something" will return null
Null & "Something" will return "Something"

You can use the same type of formula directly in the report as the source of a control.

That works a treat.

Thanks for taking the time to explain

Noz