Print Page | Close Window

Carriage return in formula and nulls

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9857
Printed Date: 06 May 2024 at 12:40am


Topic: Carriage return in formula and nulls
Posted By: Pinto
Subject: Carriage return in formula and nulls
Date Posted: 29 Apr 2010 at 2:41am

I have the following formula which works, but if one of the fields is null it causes a blank line. How can I avoid that ?

This is what I get
 
Rose Villa,
 
Watertown,
Yorkshire,
 
England,
 
I want
 
Rose Villa,
Watertown,
Yorkshire,
England
 
 
(if isnull({TblRights_Persons.Add_PropertyName})then '' else {TblRights_Persons.Add_PropertyName}+', ')+ Chr(10) +
(if isnull({TblRights_Persons.Add_PropertyNumber}) then '' else {TblRights_Persons.Add_StreetName}+', ')+ Chr(10) +
(if isnull({TblRights_Persons.Add_StreetName}) then '' else {TblRights_Persons.Add_StreetName}+' ')+ Chr(10) +
(if isnull({TblRights_Persons.Add_LocalityName}) then '' else{TblRights_Persons.Add_LocalityName}+', ')+ Chr(10) +
(if isnull({TblRights_Persons.Add_TownName}) then '' else {TblRights_Persons.Add_TownName}+', ')+ Chr(10) +
(if isnull({TblRights_Persons.Add_AreaName}) then '' else {TblRights_Persons.Add_AreaName}+', ')+ Chr(10) +
(if isnull({TblRights_Persons.Add_Postcode}) then '' else {TblRights_Persons.Add_Postcode})


Replies:
Posted By: lockwelle
Date Posted: 29 Apr 2010 at 3:19am
move your + Chr(10) inside the parenthesis...make it a part of the else...for that matter you could simplify the if statement to:
 
(if not isnull({TblRights_Persons.Add_AreaName}) then {TblRights_Persons.Add_AreaName}+', '+ Chr(10)) +
 
since you don't care what about the field if it is null.
 
HTH

Posted By: Pinto
Date Posted: 29 Apr 2010 at 3:42am
Thank you - that works fine. Is there anyway I can strip off the last comma if there is no postcode ?

Posted By: DBlank
Date Posted: 29 Apr 2010 at 6:18am
(if isnull({TblRights_Persons.Add_AreaName}) then '' else {TblRights_Persons.Add_AreaName})+
(if isnull({TblRights_Persons.Add_Postcode}) then ''
else ',' + CHR(10) + {TblRights_Persons.Add_Postcode})

Posted By: DBlank
Date Posted: 29 Apr 2010 at 6:19am

this assumes the AreaName exists.

you would have to reapply similar logic all the way up the chain to deal with where the last field stops to drop the 'last comma'.

Posted By: lockwelle
Date Posted: 29 Apr 2010 at 6:42am
the simplest solution that comes to mind would be to do something like:
local stringvar addr:="";
 
addr:= your exising statment;
addr:=trim(addr);  //just to be safe
if right(addr,1) = "," then
  addr:= mid(addr,1, len(addr)-1);
 
addr
 
this should create the variable that is everything that you are currently displaying, strip off any spaces at the end, and then check if the last character is a comma, if it is, remove it, and finally display the result.
 
HTH


Print Page | Close Window