Concatenating fields ignoring blanks

[garywood84]

In a query, I'm using concatenation to join multiple address fields (organisation, building number, street address, etc) into one field. I'm also inserting line breaks to create a formatted address and then creating a form on the query so the formatted address appears in a text box and can be copied and pasted straight into my label-writer software to print address labels.

This is the concatenation code I'm using:

Expr1: [Organisation] & Chr(13) & Chr(10) & [Building Number] & Chr(13) & Chr(10) etc.

However, one problem with which I am now presented is where a field is blank. E.g. if one of my contacts is retired, then they don't have an organisation listed in the "Organisation" field. This means that a blank line is left between their name and the rest of the address.

When running a mailmerge there is the option of skipping blank fields so that a blank line is not inserted in such cases.

How can I achieve this within the above code?

Gary

 

[WayneRyan]

Gary,

Indented for readability

Expr1: IIf(IsNull([Organisation]), 
           [Building Number] & Chr(13) & Chr(10) & [OtherField],
           [Organisation] & Chr(13) & Chr(10) & [Building Number] & Chr(13) & Chr(10) & [OtherField])

Wayne

 

[garywood84]

Wayne,

Thanks for this. However, it's not just the "Organisation" field which may be blank; I need to check to see if all fields are blank before they are included.

Presmuably I need to string together an multiple IIf statements like the one you provided to test if each cell is blank? Can you advise me how to do this?

Thanks,

Gary

 

[garywood84]

Wayne,

Further to my previous post, I've experimented a little with the code your provided and come up with a working solution!

Expr1: IIf(IsNull([Title]), "", [Title] & " ") & IIf(IsNull([First]), "", [First] & " ") & IIf(IsNull([Surname]), "", [Surname] & Chr(13) & Chr(10)) & IIf(IsNull([Organisation]), "", [Organisation] & Chr(13) & Chr(10)) & IIf(IsNull([Address1]), "", [Address1] & Chr(13) & Chr(10)) & IIf(IsNull([Address2]), "", [Address2] & Chr(13) & Chr(10)) & IIf(IsNull([Address3]), "", [Address3] & " ") & IIf(IsNull([Postcode]), "", [Postcode])

Many thanks for giving me the starting point - I'd never have figured it out without that!

Cheers,

Gary

 

[EMP]

... come up with a working solution!

Expr1: IIf(IsNull([Title]), "", [Title] & " ") & IIf(IsNull([First]), "", [First] & " ") & IIf(IsNull([Surname]), "", [Surname] & Chr(13) & Chr(10)) & IIf(IsNull([Organisation]), "", [Organisation] & Chr(13) & Chr(10)) & IIf(IsNull([Address1]), "", [Address1] & Chr(13) & Chr(10)) & IIf(IsNull([Address2]), "", [Address2] & Chr(13) & Chr(10)) & IIf(IsNull([Address3]), "", [Address3] & " ") & IIf(IsNull([Postcode]), "", [Postcode])

Alternatively, you can use the + sign instead of IIF:-

Expr1: [Title]+" " & [First]+" " & [Surname]+Chr(13)+Chr(10) & [Organisation]+Chr(13)+Chr(10) & [Address1]+Chr(13)+Chr(10) & [Address2]+Chr(13)+Chr(10) & [Address3]+" " & [Postcode]

^

 

[garywood84]

EMP,

Thanks for this. Your solution is obviously uses shorter code than mine but seems to achieve the same result.

I can't quite see how it works, though, since there doesn't appear to be anything to tell it not to include linebreaks where fields are blank?

If you can enlighten me, I'd be interested for future reference.

Gary

 

[EMP]

Gary,

My expression just makes use of two of the characteristics of the + Operator.

The following is taken from Access' help file.

[b]+ Operator

Syntax[/b]

[i]result = expression1 + expression2[/i]

[b]IF				Then[/b]
Both expressions are String 	Concatenate. 

Either expression is Null 	[i]result[/i] is Null.

EMP