Concatenating Fields with Nulls

[fsub]

Hi,

I am creating an address line by concatenating the address fields. The line does not get printed when one of the fields has a null value. Here's my code:

{Street.Number} + " " + {Street.Direction} + " " + {Street.Name} + " " + {Street.Type}.

For example, if {Street.Direction} is null, the line does not get printed. How do I get around these nulls?

Thanks

 

[Turkbear]

Hi,
You need to test for them and act accordingly:
If ANY of the fields can be null, then the test could be complex but if only direction or type are likely to be null then try somehing like

If (
IsNull({Street.Direction}) and NOT IsNull({Street.Type})
)
then
{Street.Number} + " " +{Street.Name} + " " + {Street.Type}
Else
If (
NOT IsNull({Street.Direction}) and IsNull({Street.Type})
)
then
{Street.Number} + " " + {Street.Direction} + " " + {Street.Name}
else
If (
IsNull({Street.Direction}) and IsNull({Street.Type})
)
Then
 {Street.Number} + " " +  {Street.Name} 
Else
{Street.Number} + " " + {Street.Direction} + " " + {Street.Name} + " " + {Street.Type}

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[fsub]

Hi Turkbear,

Thanks for the confirmation. That's exactly what I did and all lines now print. I tested the individual fields using the IsNull function.....quite lengthy. Apparently, formulas that concatenate fields do not work very well with nulls.

 

[Turkbear]

Hi,
I think I read in a posting here that if you have checked 'Set NULLs to Default' in the report options, the NULLs become blanks ( in strings,0s in numeric fields) and will concatenate. I don't have CR running so I can't test, however.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[fsub]

OMG! That was a much easier solution. All I had to do was to 'Convert Database Null Values to Default' in report options.

Thanks again Turkbear!

 

[Turkbear]

Hi,
Glad it helped, however that setting can cause unexpected outcomes with selection formulas etc..

This FAQ:
faq149-1217

has some examples.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[fisheromacse]

if the convert nulls causes you problems, you can use variables to somewhat simplify the IF/THEN mass.

//{@StreetFull}
numbervar snum;
stringvar sdir;
stringvar sname;
stringvar stype;

IF isnull({Street.Number}) or TRIM({Street.Number})=""
THEN snum := "" else snum := {Street.Number};

IF isnull({Street.Direction}) or TRIM({Street.Direction})=""
THEN sdir:= "" else sdir:= {Street.Direction};

IF isnull({Street.Name}) or TRIM({Street.Name})=""
THEN sname:= "" else sname:= {Street.Name};

IF isnull({Street.Type}) or TRIM({Street.Type})=""
THEN stype:= "" else stype:= {Street.Type};

snum + " " + sdir + " " + sname + " " + stype

 

[Madawc]

My usual solution for addresses is to have a separate formula field for each address line. First check for null and substitute space for it. The processed details can then be handled without the need for further checks. It also allows for comma-separated or line-separated lines, by including the relevant code at that stage.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[fsub]

I've actually employed a little of everyone's solution. Madawc's allows for clean and reliable coding. Fisheromacse and Turkbear's use of the IsNull and Trim functions are effective in querying blank or empty fields.

My report now runs like a gem. It's good to know that there's more than one solution for the same problem in Crystal. Thanks for the post everyone.