x
Solved

# concatinate string with , whilst removing duplicate ,'s for null or empty fields

Posted on 2015-02-24
Medium Priority
66 Views
Hi all

I'm wondering if its possible to concatenate a set of results with a "," but work it in such as was so that if one or more of the fields are empty/null I do not end up with multiple ,'s

I'm using addresses so for example rather than the following address showing as:
flat 4,, made up street,,,medway, kent, me1 23e

I would like it to be just single ,'s
flat 4, made up street, medway, kent, me1 23e

Regards
Neil
0
Question by:Neil Thompson

LVL 53

Accepted Solution

Gustav Brock earned 2000 total points
ID: 40627842
Yes, you can use:

=[Field1] & (", " + [Field2]) & (", " + [Field3]) & (", " + [Field4]) & (", " + [Field5])

", " + Null returns Null.
The first field must have a value.

/gustav
0

LVL 31

Expert Comment

ID: 40627864
Try this:

Type this code in a general module.

``````Public Function sqButl(strVar As String)
Dim strVarl As String
Dim finished As Boolean
strVarl = strVar
Do While Not (finished)
strVar = Replace(strVar, ",,", ",")
If strVarl = strVar Then
finished = Not (finished)
sqButl = strVar
Else
strVarl = strVar
End If
Loop
End Function
``````
Try it from the immediate window. You can call it from your code or query.

? sqBut1 ("flat 4,, made up street,,,medway, kent, me1 23e"
Result:
flat 4, made up street,medway, kent, me1 23e

The code can be improved to adjust  space after comma.
For example
0

LVL 42

Expert Comment

ID: 40629450
Gus' solution is the simplest.  It works because it takes advantage of the different concatenation behavior exhibited by the & and the +.  Fields concatenated using & will substitute a ZLS if one of them is null whereas, fields concatenated using + will return null if one of the fields is null.

So:
"a" & "," = "a,"
but
null & "," = null

Just make sure to use parentheses to control the order of precedence to ensure that you get the result you want.
0

LVL 3

Author Comment

ID: 40630293
Thanks all, I'm back in the office Friday so Ill test and award points then
0

LVL 3

Author Closing Comment

ID: 40872162
Excellent, thank you
0

LVL 53

Expert Comment

ID: 40872188
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.