Create string of text from several fields eliminating last comma


N

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!
 
Ad

Advertisements

F

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)
 
A

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.
 
N

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
 
N

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


Allen Browne said:
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.

Nancy said:
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.
 
B

Bob Barrows [MVP]

Nancy said:
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
 
Ad

Advertisements

A

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.

Nancy said:
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


Allen Browne said:
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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top