Concatenating multiple field values into one field separated by commas

[Jonibenj]

I am trying to create a list of values in a field separated by commas. I have done this in a query as follows:



[Field1]&", "&[Field2]&", "&[Field3] and so on.

However, when Field2 is null, the result is two commas between Field1 and Field2, but I only need one. What function can I use to eliminate the extra commas when fields used in the concatenation are null?

[June7]

How many fields? This could be long and complicated.

Use IIf()

Will Field1 always have data?

[Field1] & ", " & [Field2] & IIf(IsNull([Field2]), "", ",") & ...

Ooops, that won't work because if all the fields after Field2 are null will still get a comma at end. This means would need a term for each field that checks for content of all following fields. Too complicated for ControlSource expression.

Unless you are willing to live with the trailing comma.

What is this data? I suspect non-normalized structure.

[TIG_Slayer]

Maybe you could try something along these lines?

IIf(IsNull([Field1]),"",",")

EDIT: Didn't full read June7's post, as my suggestion was already stated in there

[Jonibenj]

Hi June7,

Field1 will always have data, but any of the others could be null.

The data is normalized and has the following descriptions:

Field1 - First truck transporter name
Field2 - Second truck transporter name
Field3 - Rail transporter name
Field4 - Coastal vessel name

The fields are concatenated into one to produce a transport 'string', which shows the full transport pathway for the goods.

[June7]

If fields are allowed to be empty then that is not, strictly, a normalized structured. Fully normalized would be another related table like:

OrderID	TransID	TransType
1	11	First
1	5	Second
2	3	First
2	7	Rail

A VBA procedure could then concatenate the TransID (the transporter company) data of related records. http://allenbrowne.com/func-concat.html

However, just occurred to me you can do the suggested calc in query and then handle removing the trailing comma with another expression in textbox on report.

It is a balancing act between normalization and ease of data entry/output.

[Jonibenj]

Hi June7,

I think you have misunderstood the situation. All 4 of these fields are within the same record. The records are from a table of customer orders, and these 4 fields show the transport chain for each order. So an order may be transported only by the first trucking company, whereas another order may need two trucking companies plus a coastal shipping vessel. Some orders may use a trucking company, the rail system, and a coastal vessel.

So what I want is to concatenate this transport 'chain' for each record, as the result is used in the documentation for each order.

[June7]

No, I understand the current structure - each record has 4 fields for transport info and not all 4 fields are always populated - non-normalized. My previous post showed normalized structure.

My comments are the same.

[Jonibenj]

OK! I can't change the structure of the records, so we will just have to work around it.

By the way, I notice that you reside in The Great Land - please enlighten me!

[June7]

Did you Google or Bing? That should give the answer.

[Jonibenj]

Oh, I see! However, I don't see what is so great about all that ice and snow, but I guess it is all a matter of opinion!

[June7]

What ice and snow? Very weird weather right now.