blank lines in report, clients addressess

[rob1968]

Hi,

I have create a standard letter in within my contact database, this is to send to all my clients. But some client addressess are shorter than others, for example one would have 1 Accacia Ave, London, EC1....Whereas another client would have 2 Accacia Ave, Abbey House, London, EC1

Therefore, the four lines of the second address creates a blank line in the first address, as there is no data in that address.

I have tried IIF NULL then City (Sic), but obviously on the second line on the first address this will put London on the line where it is blank/NULL....but then how do I not show the fourth line London, as this would be duplicated with the IIFNULL, and then how do I bring the postal code up a line as well?

I have looked at Northwind, but even on there they have line spaces!!!

Can anyone point me in the Direction of solving the problem?

 

[WayneRyan]

Rob,

The IIf is really your answer.
In this example, only A2 might be Null.
It could be extended to account for A1 Null as well.

Fields:

A1 - Address Line 1
A2 - Address Line 2 (May be Null)
City
Post

Address: A1 & Chr(13) & Chr(10) & IIf(IsNull(A2), City & Chr(13) & Chr(10) & Post, A2 & Chr(13) & Chr(10) & City & Post)

Wayne

 

[Big Pat]

I've done exactly this!

Word has far better formatting options that Access.

Create a Word mail merge doc, where the data source is the query you base your access report on.

Format the letter in Word exactly as you did in Access
Word will NOT print blank lines where there is nothing in the field.

E.g. <<address1>> 14 Oxford Street
<<address2>> will result in London
<<address3>> WC1A 1AA
<<postcode>>

whether "London" is in address 2 or address3