How to format a field that is sometimes blank

[demchak]

I am trying to format a field to ALWAYS be 9 chars wide. The formula below works except when the field is blank. If the field is blank it skips the field without adding the spaces.
Any help would be greatly apperciated.


If ((length(trim({NAME.CCOUNTRY})) < 5) or ((isnull({NAME.CCOUNTRY}))))
then //pad to be at least 5
{NAME.CCOUNTRY}+replicatestring(&quot; &quot;, 5-(length(trim({NAME.CCOUNTRY}))))
else
left({NAME.CCOUNTRY},5) //take 5 left most chars



PS thanks to synapsevampire for the base of this code which worked untill I got my hands on it!!

 

[demchak]

FYI the part of the code

or ((isnull({NAME.CCOUNTRY}))))

Was my attempt at making it pick up the blank fields and more than anything just makes things more messy!

 

[Ngolem]

your problem is three fold...

1. you have the test for IsNull in the wrong place...it should always come first.
2. When it is null you cannot use that field .... you should assign it 9 spaces instead
3. you say you want the results to be 9 characters but your formula doesn't do that....it creates a 5 character string
the formula below makes all results 9 characters, including blanks...also if you are going to base the length of the field value on a &quot;trimmed&quot; result then you should add a trimmed result to make sure you get the 9 characters that you want.

so to fix this we will break the IF into 2 steps

try this...I usually assign the result to a variable then display the variable at the end

whilePrintingRecords;
StringVar Result := &quot;&quot;;

If not isnull({NAME.CCOUNTRY}) then
(
if length(trim({NAME.CCOUNTRY}) < 5 then
//pad to be at least [9]
result := trim({NAME.CCOUNTRY}) +
replicatestring(&quot; &quot;,
9 - (length(trim({NAME.CCOUNTRY}))))
else
//take 5 left most chars and pad with 4 spaces
result := left(trim({NAME.CCOUNTRY}),5) + spaces(4);
)
else
// if it is null we assign 9 spaces
result := spaces(9) ;

result;


that should work now Jim Broadbent

 

[demchak]

I finally used this for the code. I think i have a few extra trim()'s in there but it works.

If Not isnull({NAME.CCOUNTRY}) then
(
If length(trim({NAME.CCOUNTRY})) < 5
then //pad to be at least 5
trim({NAME.CCOUNTRY})+replicatestring(&quot; &quot;, 5-(length(trim({NAME.CCOUNTRY}))))
else
left(trim({NAME.CCOUNTRY}),5) //take 5 left most chars
)
else space(5)