Concatenate multi fields with Commas 2

[smatthews]

Hello,

I am able to concatenate multiple fields but I would like to put a comma between the fields. However if the field is blank I just get the comma. Also, I am converting a code to a word.
Eg.
A B C = Red,White,Blue
A C = Red,Blue

Any suggestions?

Thanks in advance.

 

[rac2]

Use an IIf( ) function to determine whether the column has a value. If it is concatenate only the value; if not, concatenate a comma plus the value.

SELECT
            colA +
            IIf( colB = '', colB, ','+colB ) +
            IIf( colC = '', colC, ','+colC )

 

[smatthews]

Thanks rac2. I gave you a star!

 

[SkipVought]

rac2's solution fails if A is not present...

IIf(Left(replace([A] & "," & [B] & "," & [C],",,",","),1)=",",Right(replace([A] & "," & [B] & "," & [C],",,",","),Len(replace([A] & "," & [B] & "," & [C],",,",","))-1),
IIf(Right(replace([A] & "," & [B] & "," & [C],",,",","),1)=",",Left(replace([A] & "," & [B] & "," & [C],",,",","),Len(replace([A] & "," & [B] & "," & [C],",,",","))-1),
[A] & "," & [B] & "," & [C]
))

If you don't have a function for Replace, paste this in a VBA module ...

Function Replace(Str As String, sFind As String, sRepl As String) As String
    Dim i
    i = InStr(Str, sFind)
    If i > 0 Then
        Replace = Left(Str, i - 1) & sRepl & Right(Str, Len(Str) - Len(sFind) - (i - 1))
    Else
        Replace = Str
    End If
End Function

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[smatthews]

Thanks so much Skip. I was trying to figure out how to get rid of that preceeding comma.

 

[SkipVought]

It's more complicated than it looks.

Here's the essence (BAM!)

[b]
IIf(Left(X,1)=",",Right(X,Len(X)-1),
IIf(Right(X,1)=",",Left(X,Len(X)-1),
Y
))[/b]

where [b][ignore]

X = replace(Y,",,",",")
Y = [A] & "," & [B] & "," & [C][/ignore][/b]

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}