Print Page | Close Window

Concatenate formula when one field is empty

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=21541
Printed Date: 30 Apr 2024 at 6:58am


Topic: Concatenate formula when one field is empty
Posted By: aaml216
Subject: Concatenate formula when one field is empty
Date Posted: 10 Jun 2015 at 8:21am
I did a simple formula to combine the name fields into one, e.g. Smith, John A

{DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}& " "&
{DEMOGRAPHICS.MIDDLEINITIAL}

But it only works if the person has a middle initial. Otherwise it returns a blank field. I want the name returned regardless of whether they have a middle initial.

Thanks!


-------------
Amanda from the Mid-Atlantic


Replies:
Posted By: DBlank
Date Posted: 10 Jun 2015 at 9:24am
likely the NULL in the MI is stopping the formula fom returning anything.
either set the formula to use 'default values for null' or handle it as
 
{DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}&
(If is null {DEMOGRAPHICS.MIDDLEINITIAL} or trim({DEMOGRAPHICS.MIDDLEINITIAL})="" then "" else 
{DEMOGRAPHICS.MIDDLEINITIAL} )
 
 

Posted By: aaml216
Date Posted: 11 Jun 2015 at 3:24am
Thanks so much for taking a look at this. I am getting an error message though. All I did was to change "is null" to "isnull." I think the error it may have to do with the parentheses...? It is saying that after the isnull portion, it is ready to see a "then."

What is the purpose of the trim statement?

-------------
Amanda from the Mid-Atlantic

Posted By: kevlray
Date Posted: 11 Jun 2015 at 5:00am


Personally I have good success with the following setup.
{DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}&(if len({DEMOGRAPHICS.MIDDLEINITIAL}) >0 then " " & {DEMOGRAPHICS.MIDDLEINITIAL} else "")

Posted By: aaml216
Date Posted: 11 Jun 2015 at 5:38am
kevlray - Sorry, I still only get patients who have middle initials...

-------------
Amanda from the Mid-Atlantic

Posted By: DBlank
Date Posted: 11 Jun 2015 at 8:34am
Sorry, I inserted some SQL ...

(If isnull({DEMOGRAPHICS.MIDDLEINITIAL}) or trim({DEMOGRAPHICS.MIDDLEINITIAL})="" ...

The trim is accounting for any middle initials that someone stuck in a space instead of an empty string.

Kevlray's code will work if you set the formula field to use default values for nulls

Posted By: aaml216
Date Posted: 11 Jun 2015 at 8:38am
So the ... is:

then {DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}& " " & {DEMOGRAPHICS.MIDDLEINITIAL}

Sorry to be so dense. I am pretty new to this.

-------------
Amanda from the Mid-Atlantic

Posted By: DBlank
Date Posted: 11 Jun 2015 at 10:43am

{DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}&
(If isnull({DEMOGRAPHICS.MIDDLEINITIAL}) or trim({DEMOGRAPHICS.MIDDLEINITIAL})="" then "" else
{DEMOGRAPHICS.MIDDLEINITIAL} )



Print Page | Close Window