Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Carriage return in formula and nulls Post Reply Post New Topic
Author Message
Pinto
Newbie
Newbie
Avatar

Joined: 14 Sep 2007
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote Pinto Replybullet Topic: Carriage return in formula and nulls
     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})
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet 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
IP IP Logged
Pinto
Newbie
Newbie
Avatar

Joined: 14 Sep 2007
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote Pinto Replybullet 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 ?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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'.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet 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
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.