Create string of text from several fields eliminating last comma

[Nancy]

Hi,

I have a query that has several columns (from a table) that I want to join
as a comma deliminted string in a new field. Not all columns contain data. I
can create the new field with the text values from the columns using "&". I
need to know how to eliminate the last comma in the string because the query
will be used to build a new table with additional fields. This new table
will be exported as text and comma delimited.

I appreciate any help that you can provide!

 

[fredg]

Hi,

I have a query that has several columns (from a table) that I want to join
as a comma deliminted string in a new field. Not all columns contain data. I
can create the new field with the text values from the columns using "&". I
need to know how to eliminate the last comma in the string because the query
will be used to build a new table with additional fields. This new table
will be exported as text and comma delimited.

I appreciate any help that you can provide!

ColumnName = Left(ColumnName,Len(ColumnName)-1)

 

[Allen Browne]

Try an expression like this:
=[f1] & ", " + [f2] & ", " + [f3] & ", " + [f4]
using your field names in place of f1, f2, f3, and f4.

There is a subtle difference between the 2 concatenation operators in
Access:
"A" + Null => Null
"A" & Null => "A"

Therefore F1 stands on its own (before the ampersand.)
The ", " + [f2] will be Null if F2 is null; otherwise the comma and F2 get
printed.
And so on.

 

[Nancy]

You didn't specifiy if this was done on an update query or as an additional
column in the original query but the result was either Error or -1.

Nancy

 

[Nancy]

Thanks for your input. The expression did indeed eliminate the last comma.
However, because not all the columns have test data the commas between still
show in the new field.

Do you have any more ideas?

--
Nancy

Try an expression like this:
=[f1] & ", " + [f2] & ", " + [f3] & ", " + [f4]
using your field names in place of f1, f2, f3, and f4.

There is a subtle difference between the 2 concatenation operators in
Access:
"A" + Null => Null
"A" & Null => "A"

Therefore F1 stands on its own (before the ampersand.)
The ", " + [f2] will be Null if F2 is null; otherwise the comma and F2 get
printed.
And so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that has several columns (from a table) that I want to join
as a comma deliminted string in a new field. Not all columns contain
data. I
can create the new field with the text values from the columns using "&".
I
need to know how to eliminate the last comma in the string because the
query
will be used to build a new table with additional fields. This new table
will be exported as text and comma delimited.

 

[Bob Barrows [MVP]]

Hi,

I have a query that has several columns (from a table) that I want to
join as a comma deliminted string in a new field. Not all columns
contain data. I can create the new field with the text values from
the columns using "&". I need to know how to eliminate the last
comma in the string because the query will be used to build a new
table with additional fields. This new table will be exported as
text and comma delimited.

I appreciate any help that you can provide!

SELECT
iif(right([joined_data],1)=","
, left([joined_data],len([joined_data]-1))
,[joined_data]) as withoutcomma
FROM (
SELECT [col1] & ',' & ... & [colN] as joined_data from ...) as q

 

[Allen Browne]

If that is so, then your fields don't contain Null.

Perhaps they contain zero-length strings, spaces, or other things that
interfere. If so, you will need to test each one with an IIf() expression.
It will get long and messy, but you can do it.

Or, you could sort out whatever values you have where it should be Null. The
suggested expression does work then the fields are null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your input. The expression did indeed eliminate the last
comma.
However, because not all the columns have test data the commas between
still
show in the new field.

Do you have any more ideas?

--
Nancy

Try an expression like this:
=[f1] & ", " + [f2] & ", " + [f3] & ", " + [f4]
using your field names in place of f1, f2, f3, and f4.

There is a subtle difference between the 2 concatenation operators in
Access:
"A" + Null => Null
"A" & Null => "A"

Therefore F1 stands on its own (before the ampersand.)
The ", " + [f2] will be Null if F2 is null; otherwise the comma and F2
get
printed.
And so on.

I have a query that has several columns (from a table) that I want to
join
as a comma deliminted string in a new field. Not all columns contain
data. I
can create the new field with the text values from the columns using
"&".
I
need to know how to eliminate the last comma in the string because the
query
will be used to build a new table with additional fields. This new
table
will be exported as text and comma delimited.