Solved

# Formula for concatenating multiple fields with "commas" to seperate values

Posted on 2016-08-03
33 Views
Gurus,

I could use some help figuring out how to write a formula for this problem:

I have 6 separate currency fields to concatenate in one field.  I have to eliminate values of 0.

I wrote a formula like this:
if isnull({field 1}) or {field 1} <= 0 then "" else Cstr({field 1}, 0, "") & "% Fiber"

I can't figure out how to include the commas (,) to separate more than one value or eliminate the comma if there's only one value.

The result would display something like this:
10% Fiber, 5% Protein

...or in cases where there's only one value:
10% Fiber

Can you help?

Steve
0
Question by:SStroz
• 2
• 2

LVL 100

Accepted Solution

mlmcc earned 500 total points
Try this formula

``````Local StringVar strOut;

if Not(isnull({field 1})) AND  {field 1} > 0 then
strOut := " " & Cstr({field 1}, 0, "") & "% Fiber";
if Not(isnull({field 1})) AND  {field 2} > 0 then
strOut := ", " & Cstr({field 2}, 0, "") & "% Protein";
if Not(isnull({field 1})) AND  {field 3} > 0 then
strOut := ", " & Cstr({field 3}, 0, "") & "% String3";
if Not(isnull({field 1})) AND  {field 4} > 0 then
strOut := ", " & Cstr({field 4}, 0, "") & "% String4";
if Not(isnull({field 1})) AND  {field 5} > 0 then
strOut := ", " & Cstr({field 5}, 0, "") & "% String5";
if Not(isnull({field 1})) AND  {field 6} > 0 then
strOut := ", " & Cstr({field 6}, 0, "") & "% String6";

Mid(strOut,2)
``````

mlmcc
0

LVL 7

Author Comment

Thanks SO much mlmcc!

I needed them to all be added together so I just added a "strOut"

strOut := strOut + ", " & Cstr({field 2}, 0, "") & "% Protein";

Thanks for pointing me in the right direction!!!

Steve
0

LVL 7

Author Closing Comment

Thanks mlmcc!!!
0

LVL 100

Expert Comment

Sorry, that should have been in the solution

mlmcc
0

## Featured Post

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…