Concatenate fields with null
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2422
Printed Date: 04 May 2024 at 7:36am
Topic: Concatenate fields with null
Posted By: dfolzenlogen
Subject: Concatenate fields with null
Date Posted: 23 Feb 2008 at 5:36pm
|
I'm trying to build a report using some very incomplete data provided to me. For the life of me -- I can't figure out how to format it the way I need it. Here's what I'm trying to do:
//I'm trying to concatenate the fields to be inserted into a line on a contract form showing the "name, company, address 1, address 2, city, state zip". //I've set up a formula field to be embedded formula in a text box which contains the agreement.
//The simple concatenation formula is: {NAME} & ", " & {COMPANY NAME} & ", " & {ADDRESS 1} & ", "& {ADDRESS 2} & ", " & {CITY} & ", " & {STATE} &" " & {ZIP}
//It needs to provide that if the field is empty an empty string is inserted. //Want to also eliminate the punctuation after empty fields //The fields to be concatenated are all formatted as strings I have tried using IsNull and also assigning emply fields as "" but can't seem to get any formulats to work. Am I missing something simple? |
Replies:
Posted By: BrianBischof
Date Posted: 23 Feb 2008 at 5:43pm
When doing something like this, I like to use the IIF() function within my concatenation formula. The IIF() function returns one value when a condition is true and another value when false.IIF(IsNull({CompanyName}), "", {CompanyName} & ",")THis code returns "" if it is null, otherwise it returns the field with a comma at the end. You'll need to concatenate all these together and still play with the commas a bit to get the formatting perfect, but that is the way I would approach it. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: dfolzenlogen
Date Posted: 23 Feb 2008 at 5:47pm
Thank you for the quick reply. Will give it a go!!
|
Posted By: dfolzenlogen
Date Posted: 23 Feb 2008 at 7:27pm
|
Worked like a charm!! |
Posted By: BrianBischof
Date Posted: 23 Feb 2008 at 8:24pm
|
Glad to hear it. I have all the Crystal Reports formulas and sample code document in three chapters in my book http://www.amazon.com/exec/obidos/ASIN/0974953601/bischofsystem-20 - Crystal Reports Encyclopedia .
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: dfolzenlogen
Date Posted: 24 Feb 2008 at 2:59am
|
Brian --
I actually bought your book last year. Has tons of great info. I would have referred to it but a friend borrowed the book a few weeks ago and has not returned it.
|
Posted By: lhoehne
Date Posted: 21 Jul 2008 at 9:55am
|
I tried using this and am concatenating the fields together. It does not appear to be working. Here's what I have. Any suggestions?
IIF(IsNull({nomination.recparty1}), "", {nomination.recparty1} & ",") &
IIF(IsNull({nomination.recparty2}), "", {nomination.recparty2} & ",") & IIF(IsNull({nomination.recparty3}), "", {nomination.recparty3} & ",") |
Posted By: tryck
Date Posted: 01 Jul 2010 at 7:17am
|
Solution for your problem: http://www-01.ibm.com/support/docview.wss?uid=swg21143931 |