Concatenate with Nz

[steve21nj]

In this example, I'm working with concatenated values.
CBNOPREF = "No Pref"
CBWORD = "Word"
CBEXCEL = "Excel"

So the if my data had each it would show (No Pref;Word;Excel)
That's fine until my data only has excel, and would show (;;Excel)

How would I had Nz to the following to eliminate the blank ";"?

DESIREDFORMAT: [CBNOPREF] & ";" & [CBWORD] & ";" & [CBEXCEL]

 

[plog]

This isn't a problem to solve with NZ its an issue to solve with IIF. You need to add a semi-colon only where there is un-semicoloned data before the value you want to add.

I would do this in a custom function. I would pass it all the values you want to concatenate and then pass them into a return variable one at a time, adding semi colons as necessary:

Dim ret AS String

ret=Value1

if (IsNull(Value2)=False) then 
    if (isnull(ret)= False Then ret = ret & ";"
    ret = ret & Value2
    End If

if (IsNull(Value3)=False) then 
    if (isnull(ret)= False Then ret = ret & ";"
    ret = ret & Value3
    End If
.
.
.

This can be more elegant using an array and a loop, but that's the gist.

 

[JANR]

How about null propagation:

DESIREDFORMAT: ([CBNOPREF]+";") & ([CBWORD]+";") & ([CBEXCEL])

If any result inside the presens is NULL the hole expression is evaluated to null

Just my 2 cents

JR

 

[plog]

Not a bad idea JANR, but if CBEXCEL is null it would end with a semi-colon as the last character, don't know if that's what he wants.

 

[JANR]

Not a bad idea JANR, but if CBEXCEL is null it would end with a semi-colon as the last character, don't know if that's what he wants

Yeah you are right, but how about:

DESIREDFORMAT: Replace(Trim(([CBNOPREF]) & (" "+[CBWORD]) & (" "+[CBEXCEL]))," ",";")

You insert a space between the elements, trim surplus space and finally insert your seperator of choice.

JR

 

[steve21nj]

Great responses, thank you all!