Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2016-08-03
4
49 Views
Last Modified: 2016-08-05
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?

Thanks in advance!!

Steve
0
Comment
Question by:SStroz
  • 2
  • 2
4 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 41741642
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)

Open in new window


mlmcc
0
 
LVL 7

Author Comment

by:SStroz
ID: 41741756
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

by:SStroz
ID: 41741757
Thanks mlmcc!!!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41745108
Sorry, that should have been in the solution

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question