Help displaying Addresses with blank fields

[Dreamcatcher]

Hi,
Newbie question. Can someone please help me in the best way to display address fields on a report, where some of the address fields are blank. Want to show the address eliminating the spaces of blank data.
I.e I've attached a sample mdb to help explain.
I've got a table with Address1, Address2, Address3, Address4, County, Postcode and a simple report showing the sample data. If Address3 is blank then the report shows -

Address1
Address2

Address4
County
Postcode

Want it to be

Address1
Address2
Address4
County
Postcode

How can I eliminate all spaces

Thanks

 

[Trevor G]

You would need to use a query and then add the fields into the query then add an expression and you can use this formula. I do this on a system works a treat then on the report you add the expression field as the Address:

Address: IIf([Booking Address 1] Is Not Null,[Booking Address 1] & Chr(13) & Chr(10),Null) & IIf([Booking Address 2] Is Not Null,[Booking Address 2] & Chr(13) & Chr(10),Null) & IIf([Booking Address 3] Is Not Null,[Booking Address 3] & Chr(13) & Chr(10),Null) & IIf([Booking Address 4] Is Not Null,[Booking Address 4] & Chr(13) & Chr(10),Null) & IIf([Booking Address 5] Is Not Null,[Booking Address 5] & Chr(13) & Chr(10),Null) & IIf([Booking Address 6] Is Not Null,[Booking Address 6] & Chr(13) & Chr(10),Null)

 

[Beetle]

You would need to use a query and then add the fields into the query then add an expression and you can use this formula. I do this on a system works a treat then on the report you add the expression field as the Address:

Quote:
Address: IIf([Booking Address 1] Is Not Null,[Booking Address 1] & Chr(13) & Chr(10),Null) & IIf([Booking Address 2] Is Not Null,[Booking Address 2] & Chr(13) & Chr(10),Null) & IIf([Booking Address 3] Is Not Null,[Booking Address 3] & Chr(13) & Chr(10),Null) & IIf([Booking Address 4] Is Not Null,[Booking Address 4] & Chr(13) & Chr(10),Null) & IIf([Booking Address 5] Is Not Null,[Booking Address 5] & Chr(13) & Chr(10),Null) & IIf([Booking Address 6] Is Not Null,[Booking Address 6] & Chr(13) & Chr(10),Null)

In a scenario like this you could take advantage of the + concatenation operator, which propagates Nulls. You could use a mix of the + and & operators to create a much simpler expression;

Addresses: ([Address1]+Chr(13)+Chr(10)) & ([Address2]+Chr(13)+Chr(10)) & ([Address3]+Chr(13)+Chr(10)) & ([Address4]+Chr(13)+Chr(10))

 

[Dreamcatcher]

Thank you both for your advice. They worked a treat and have helped improve my learning of access. Cheers

 

[Trevor G]

Pleased to read you have a solution and thanks for posting back.