Concatenate Query - seperator problem

D

Dean Fraiquin

Hi there,

Have to work on a database and have worked out how to join the contents of
three columns to make a single text string. This is the expression I've used
in the query:

act: Trim([ACT_1] & " " & [ACT_2] & " " & [ACT_3])

This works great with one little problem - I would like to use a ',' to
separate each entry, however some records only have data in 1 or 2 of the
original three columns - so is there a way of using a conditional (?) format
so if there is something in ACT_2 and/or ACT_3 it shows the ',' but if
nothing in there it doesn't?

Many thanks for your anticipated help.
 
A

Allen Browne

Try:
act: [ACT_1] + ", " & [ACT_2] + ", " & [ACT_3]

This relies on a subtle difference between the 2 concatenation operators in
Access:
"A" & Null => "A"
"A" + Null => Null

It is easier than using IIf().
 
M

Marshall Barton

Hi there,

Have to work on a database and have worked out how to join the contents of
three columns to make a single text string. This is the expression I've used
in the query:

act: Trim([ACT_1] & " " & [ACT_2] & " " & [ACT_3])

This works great with one little problem - I would like to use a ',' to
separate each entry, however some records only have data in 1 or 2 of the
original three columns - so is there a way of using a conditional (?) format
so if there is something in ACT_2 and/or ACT_3 it shows the ',' but if
nothing in there it doesn't?
Click to expand...


See if this does what you want:

act: [ACT_1] & ("." + [ACT_2]) & ("." + [ACT_3])
 
D

Dean Fraiquin

Many thanks for the quick response - unfortunately it only slightly worked!
If there is a record with only one entry in ACT_1 it will bring in a single
',' after the entry rather than two of them, but it is still an unnecessary
',' - and if there are only entries in ACT_1 & ACT_2 it still brings in a ','
after the ACT_2 entry.

I hope that makes sense!

Allen Browne said:
Try:
act: [ACT_1] + ", " & [ACT_2] + ", " & [ACT_3]

This relies on a subtle difference between the 2 concatenation operators in
Access:
"A" & Null => "A"
"A" + Null => Null

It is easier than using IIf().

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

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

Dean Fraiquin said:
Hi there,

Have to work on a database and have worked out how to join the contents of
three columns to make a single text string. This is the expression I've
used
in the query:

act: Trim([ACT_1] & " " & [ACT_2] & " " & [ACT_3])

This works great with one little problem - I would like to use a ',' to
separate each entry, however some records only have data in 1 or 2 of the
original three columns - so is there a way of using a conditional (?)
format
so if there is something in ACT_2 and/or ACT_3 it shows the ',' but if
nothing in there it doesn't?

Many thanks for your anticipated help.
Click to expand...
Click to expand...
 
D

Dean Fraiquin

Brilliant! Many thanks to both of you for your quick help!

Marshall Barton said:
Hi there,

Have to work on a database and have worked out how to join the contents of
three columns to make a single text string. This is the expression I've used
in the query:

act: Trim([ACT_1] & " " & [ACT_2] & " " & [ACT_3])

This works great with one little problem - I would like to use a ',' to
separate each entry, however some records only have data in 1 or 2 of the
original three columns - so is there a way of using a conditional (?) format
so if there is something in ACT_2 and/or ACT_3 it shows the ',' but if
nothing in there it doesn't?
Click to expand...


See if this does what you want:

act: [ACT_1] & ("." + [ACT_2]) & ("." + [ACT_3])
Click to expand...
 
Top