Concatenate in query with line feeds

[waxwing]

Hello -
I have five fields in table that need to be concatenated into one field
with line feeds. The result in this new field is merged into a
PowerPoint text box and the line feeds allows PPT to add bullets. My
update query works fine as long as I have data in each of the five
fields. Sometimes the first three fields have data but the last two
are blank; This would create two empty bullet points. Sometimes, the
middle field is empty but the two on either end have data.

Any ideas on a update query or formula that would combine only the
fields with data and ensure that there are the appropriate amount of
line feeds (i.e. only between two bullet points but never after the
last or before the first one)?

Here's my formula so far...

[Field1]&Chr(13)&[Field2]&Chr(13)&[Field3]&Chr(13)&[Field4]&Chr(13)&[Field5]

Should I use this formula in a query then another that would strip out
the extra line feeds?

Any help would be appreciated.

- John

 

[Van T. Dinh]

Normally in Access, for a new paragraph you need Chr(13) & Chr(10) in this
order.

However, if PPT is happy with Chr(13) only, try:

[Field1] & Chr(13) & [Field2] & Chr(13) & [Field3] &
( Chr(13) + [Field4]) & (Chr(13) + [Field5])

provided that Field4 & Field5 have Null value and not empty String.

HTH
Van T. Dinh
MVP (Access)

 

[waxwing]

Thanks, Van. That worked great. Could you tell me why it works? If
you add (+) something to a null value does it return a null?

- John

 

[Van T. Dinh]

Yep. That the difference between & and +.

The concatenation operator "+" propagates Null, meaning:

{something} + Null results in Null

while the concatenation operator "&" doesn't, i.e.:

{something} & Null results in {something}

HTH
Van T. Dinh
MVP (Access)