Crystal Reports - Concatinate fields where at least one is null

[cliffs]

Hi, I have 5 fields, Company; Title; Forename; Othername and Surname, and I want to join them together. I've created a formula field, companypersonname, and used the following formula:

TRIM({Company}&" "&{Title}&" "&{Forename}&" "&{Othername}&" "&{Surname})

but while the report does run, the field, companypersonname, doesn't populate.

I've supplied an example of what I'm aiming for in the attachment.

Any idea's where I'm going wrong?

Many thanks, Cliff

[DellSC]

The problem is the nulls - they can't be treated like regular data because of the nature of what null means. They have no value.

So, you have a couple of options:

1. In the Formula Handler, set null handling to "Default values for Null". This will make your nulls into empty strings that will concatenate correctly. However, you'll have extra spaces in your result.

2. Control the null handling yourself and change the formula to something like:

Local Stringvar Co := "";
Local Stringvar Title := "";
Local Stringvar Fore := "";
Local Stringvar Other := "";
Local Stringvar Sur := "";

If not IsNull({Company}) then Co := {Company} & " ";
If Not IsNull({Title}) then Title := {Title & " ";
If Not IsNull({Forename}) then Fore := {Forename} & " ";
If Not IsNull({Othername}) then Other := {Othername} & " ";
If Not IsNull({Surname}) then Sur := {Surname};
TRIM(Co & Title & Fore & Other & Sur)

-Dell

[ido_millet]

The result of operations on Null components is Null.

Check for null and return a string instead by using IsNull() function.