Concatenate fields

[Sylviajb]

I've set up a query to concatenate fields: Name_ID:[compl01]&","&[Compl02]&","&[Compl03]
But this returns as each field in a separate row in stead of in one field.
Also how do I exclude fields with no value in it.?
What is the maximum number of fields that can be concatenated?

 

[pr2-eugin]

Hello, what do you mean..

But this returns as each field in a separate row in stead of in one field.

can you show a sample of what is happening??

Also how do I exclude fields with no value in it.?

Lookup on the use of Nz() function

What is the maximum number of fields that can be concatenated?

Do not think there is a limit.. but I might be wrong, as I never attempted to test this nor research this..

 

[YNWA]

My one is:

ID: [ClientName] & ", " & [Address1] & ", " & [Address2]

Tried your example and it gives me same 1 field answer.

Are you using a select query?

 

[JANR]

Also how do I exclude fields with no value in it.?

For this you can use Nullpropagation:

Name_ID: (compl01 + ",") & (compl02 + ",") & (compl03)

Any field inside the perens that evaluates to null the whole expression inside the perens gets evaluated to null (it dosen't show)

However fields like compl01, compl02 ect reeks of a denormalized structure, and should be in a seperate table.

JR