Formula to concatenate field items into a string? CR 8.5, Crystal Reports Formula Editor

Posted on 2004-10-10
Last Modified: 2011-10-03

Re: Crystal Reports Formula Editor

I have created a subreport on a Crystal Reports 8.5 report, based on a "stored procedure" (MS Access Proc query) "qryAllAliasNamesByGID". The query returns records that have only 1 field each: a string that is the name of a color.

For example, let's say I returned 3 records, yielding the following 3 strings:

What I want on the report would be this concatenated string, as a single line of text:
"My colors are: (Crimson, Ruby, Burgundy)"

1.) I don't know how to step through all the returned records.
2.) I don't know if any of this Crystal Reports Formula Editor (basic) syntax is correct:

Dim strFinal as string
Dim strAliases as string

While NOT({qryAllAliasNamesByGID}.EOF) 'The formula checker throws an error at this line, says: "This field name is not known"
    strAliases = {qryAllAliasNamesByGID.strGroupName} & ", "   

strAliases = Trim(strAliases)
'kill the final comma:
If Asc(Right(strAliases,1) = 44 Then 'last character is a comma
    strAliases = Left(strAliases,(Len(strAliases) -1))
End If

strFinal = "My Colors are: (" & strAliases & ")"


Thanks VERY MUCH, in advance, for any help. Oh, and 500 great big points...

Question by:dtleahy
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
  • 3
  • 2
LVL 100

Accepted Solution

mlmcc earned 500 total points
ID: 12274236
You cannot loop through the recordset.

Try this

In the report header insert a formula
Name - DeclVars
shared stringvar MyString := "";

In the detail section add another formula
Name - ConcatStrings
shared stringvar MyString;
if MyString = "" then
  Mystring := {Table.FieldWithString};
  Mystring := MyString & ", " & {Table.FieldWithString};

In the Report Footer add another formula
Name - PrintLine
shared stringvar MyString;
if MyString = "" then
  "No recordset Returned"
  "My Colors are: (" & MyString & ")"


Author Comment

ID: 12283019
Thanks to mlmcc for the reply.

I can't get your solution to work...

Step 1: with my subreport open, created the DeclVars formula, placed an instance in the report header of my subreport
Step 2: tried to create the ConcatStrings formula,as follows, but got an error while checking syntax:
shared stringvar MyString;
if MyString = "" then
  Mystring := {qryAllAliasNamesByGID.strGroupName}; 'here I get an error when I check: "The remaining text does not appear to be part of the formula"
  Mystring := MyString & ", " & {qryAllAliasNamesByGID.strGroupName};

I have an existing report (staying with the example of colors) that returns "Red" with a full report about all the fields related to Red, and a photo of Red. This data came from a query that includes multiple "Red Items", where each record returned has data for an additional item on a detail line of the main report.

I am using a query to return records for the report. It was working fine, displaying all the data regarding "Red". Now I want to enhance the report, and add a list of the aliases of Red to my report. To get the list of aliases to Red (which are individual records in a table), I figured I had to leave my existing report alone, and add a subreport to show the concatenated string from a different query. In terms of formatting on the report, a subreport will work out fine. (In other words, I don't need that alias data to show up in preexisting detail lines.)

Can you tell me where I'm going wrong?



Author Comment

ID: 12283041

I also tried Step 1 above, with the Main report open, creating the formula in the main report. I tried to create the formula in step 2 both in the main report, and in the subreport...

Same error...

Author Comment

ID: 12283528
OK, I got it. Your answer was off by a single semicolon.

Should have been:
In the detail section add another formula
Name - ConcatStrings
shared stringvar MyString;
if MyString = "" then
  Mystring := {Table.FieldWithString}
  Mystring := MyString & ", " & {Table.FieldWithString};

Thank you very much for your help. 500 points!
LVL 100

Expert Comment

ID: 12283647
Glad i could help


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 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