Editing the Results of an Expression Concatenation

[cwagenaar]

Hi there,



I am trying to concatenate the results of several fields into one field, with each result separated by a comma. However, some fields are empty, so I don't want there to be commas with nothing between (as I have to export the query as an Excel document, and then as a CSV). Here's an example of what I'm trying to do:

Expr 1: Iif(IsNull([Roles]![Expression]=True),"","Adminstrator, ") & Iif(IsNull([Roles]![Expr2]=True),"","Content Manager, ")

That's just part of it, there are a couple more fields involved, and then I'll be doing a similar one for even more fields. Now, I've been trying to figure out a way to get rid of any unnecessary commas - I can't nest the Iif functions, because I may need the results of all of the fields, or just some of the fields, in this one expression. I could not find any expression functions that really met my needs, so I was attempting to write a new function in VBA, but I am failing miserably, having not touched Visual Basic in almost two years. It doesn't help, either, that if a person wants to use VBA in Access they have to define an Object and that is well outside of the realm of what I recall from VBA.

I apologize if this thread is an ill fit for this forum, and should in fact be in the Programming forum. I've been at this for days and am really at a loss, so if anyone has any suggestions I would greatly appreciate them.

[June7]

Looks like misplaced ) for the IsNull function.

IIf(IsNull([Expression]), "", "Administrator, ") & IIf(IsNull([Expr2]), "", "Content Manager, ")

Problem is will always have a comma at the end if there is at least one value.

Don't always have to define an object in VBA.

[ranman256]

Use a function. Paste this code into a module.
then in your query the Concated field would be: ConCatFld: BuildFld([Adm], [Mngr],[CEO])


Put all the fields needed as parameters to be checked:

public function BuildFld(pvAdm, pvMngr,pvCEO)
dim vWord

if not IsNull(pvAdm) then vWord = vWord & pvAdm & ","       '"Adminstrator"
if not IsNull(pvMngr) then vWord = vWord & pvMngr & ","       ' "Content Manager, "
if not IsNull(pvCEO) then vWord = vWord & pvCEO & ","       '"CEO, "




'REMOVE LAST COMMA IF NEEDED
vWord = left(vWord, len(vWord) - 2)


BuildFld = vWord
end function

[June7]

Probably need to include some code that first checks if the concatenation results in Null because will error if it is.

[cwagenaar]

Thanks, this seems to work really well! The only problem is that, when all of the fields return null, it seems to return commas. I'll share a screenshot:



Any thoughts? If I can figure out how to get rid of those commas in the null fields, I'll be able to modify this piece of code to fit my needs for another expression, as well.

Thanks again for all the help!

[June7]

If you are getting commas then the variables are not actually Null, they have something, likely an empty string. Alternative code to handle that:

If pvAdm & "" <> "" Then vWord = vWord & pvAdm & ", "

[cwagenaar]

If you are getting commas then the variables are not actually Null, they have something, likely an empty string. Alternative code to handle that:

If pvAdm & "" <> "" Then vWord = vWord & pvAdm & ", "

Thank you! That solved it. I also had to use a variation of that for the end bit to trim any unnecessary commas/spaces.

If vWord <> "" Then vWord = Left(vWord, Len(vWord) - 2)