Concatenate fields, but not if null

[JE]

I would like to concatenate a series of fields, say Att1, Att2, Att3, and Att4.
I would like a comma between each one.
If the field(s) is/are null, I do not want a comma and space added.

Is there an example of this kind of funcitonality somewhere that I can look
at, or can someone give me a code snippet that I could expand for more
attributes?

Thanks.

 

[Douglas J. Steele]

There's a difference between using + and & as concatenation characters that
you can take advantage of.

Null + anything results in Null, Null & anything results in anything. That
means you can use:

Att1 & (", " + Att2) & (", " + Att3) & (", " + Att4)

 

[JE]

Thanks Doug works like a champ, except if the field is null, the query
displays the comma; is there a way to not display the commas as well when
null?

 

[John Spencer]

Alternative to Douglas Steele's solution is to write a custom VBA function.
The advantage is that the function can handle the first field being empty
better. For instance, if Att1 is Null, and Att2 is "ARB" and the remaining
fields were null, the result would be ", Arb".

Here is a VBA function you can paste into a VBA module and call when needed.

In a query you would call it like:
Field: Combined: fConcatFieldsInRow(", ",[Att1],[Att2],[Att3],[Att4])

'===================== Code Begins =========================
Public Function fConcatFieldsInRow(sDelimit As String, _
ParamArray varValues()) As String
'***********************************************************
'Name: fConcatFieldsInRow (Function)
'Purpose: Return a string that contains values separated
' by a specified delimiter
'Author: John Spencer
'Date: June 26, 2008
'Inputs: sDelimit - delimiter between values
' varValues - series of values to be concatenated
'Output: string of values separated by line feeds
'***********************************************************

Dim i As Integer, strReturn As String

For i = LBound(varValues) To UBound(varValues)
If Len(varValues(i) & "") > 0 Then
strReturn = strReturn & varValues(i) & sDelimit
End If
Next

If Len(strReturn) > 0 Then
strReturn = Left(strReturn, Len(strReturn) - Len(sDelimit))
End If

fConcatFieldsInRow = strReturn

End Function
'===================== Code Ends =========================


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

The only way you should get an unnecessary comma is if Att1 is Null and one
of the other three isn't.

You might try John's function.