Concatenate Query - seperator problem

  • Thread starter Thread starter Dean Fraiquin
  • Start date Start date
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.
 
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().
 
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])
 
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...
 
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...
 
Back
Top