Need help with If Then Else or Nested If Then Else

[GaryCWL]

Backend: SQL Server 2000
Crystal Version: XI

I have a total of 10 columns that I'm trying to concatenate; however, whether or not I concatenate 2, 4, 6, 8, or all 10 columns would depend if the source column is null.

For example: My data consists of the following tables and columns:
{Part.Dim1} - source lookup
{Part.Dim2} - source lookup
{Part.Dim3} - source lookup
{Part.Dim4} - source lookup
{Part.Dim5} - source lookup
{Category.Var1}
{Category.Var1}
{Category.Var1}
{Category.Var1}
{Category.Var1}

I want to concatenate Dim1 with Var1 and then all 5 groupings (Dim2 with Var2; Dim3 with Var3, etc) only if the originating Dim column(s) is/are not null.

For example: If non of the Dim columns were null for a part then I would just do:
{Part.Dim1} & " " & {Category.Var1} & " " & {Part.Dim2} & " " & {Category.Var2} & " " & {Part.Dim3} & " " & {Category.Var3} & " " & {Part.Dim4} & " " & {Category.Var4} & " " & {Part.Dim5} & " " & {Category.Var5}

If Dim2 is null then I could concatenate all except Dim2 and Var2 like:
{Part.Dim1} & " " & {Category.Var1} & " " & {Part.Dim3} & " " & {Category.Var3} & " " & {Part.Dim4} & " " & {Category.Var4} & " " & {Part.Dim5} & " " & {Category.Var5}

If Dim2 and Dim5 were null then I would not include Dim2, Var2, Dim5, and Var5 with the concatenation like
{Part.Dim1} & " " & {Category.Var1} & " " & {Part.Dim3} & " " & {Category.Var3} & " " & {Part.Dim4} & " " & {Category.Var4}

How would I write an If Then Else statement that would handle this?

 

[synapsevampire]

Try:

whileprintingrecords;
stringvar output:="";
if not isnull({Part.Dim1}) then
output:=output + {Part.Dim1} & " " & {Category.Var1} & " ";
if not isnull({Part.Dim2}) then
output:=output + {Part.Dim2} & " " & {Category.Var2} & " ";
if not isnull({Part.Dim3}) then
output:=output + {Part.Dim3} & " " & {Category.Var3} & " ";
if not isnull({Part.Dim4}) then
output:=output + {Part.Dim4} & " " & {Category.Var4} & " ";
if not isnull({Part.Dim5}) then
output:=output + {Part.Dim5} & " " & {Category.Var5};
trim(Output)

-k

 

[GaryCWL]

synapsvampire,

Thank you for directing me in the right spot. However, when I added the formula field to my report, I still get the extra blank space (" ") where one of the Dim field's were null. Is there a way to exclude the "output:=output...." if the Dim field is null?

My formula looks like:

whileprintingrecords;
stringvar output:="";
if not isnull({CO_ITEM_MASTER.DIM_5}) then
output:=output + {CO_ITEM_MASTER.DIM_5} & " " & {CO_ITEM_MASTER_VAR.VAR_24} & " ";
if not isnull({CO_ITEM_MASTER.DIM_1}) then
output:=output + " x " + {CO_ITEM_MASTER.DIM_1} & " " & {CO_ITEM_MASTER_VAR.VAR_22} & " ";
if not isnull({CO_ITEM_MASTER.DIM_3}) then
output:=output + " x " + {CO_ITEM_MASTER.DIM_3} & " " & {CO_ITEM_MASTER_VAR.VAR_23} & " ";
trim(Output)

A snippet of my result set looks like:

9 Inches x 0.75 Inches x
x x
x x
x x 2.5 Inches
x x 2.5 Inches
9.96 Feet x 9 Inches x 1.5 Inches
x x
9.96 Feet x 12 Inches x 1.5 Inches

Based on the sample above, I would like to only return:

9 Inches x 0.75 Inches


2.5 Inches
2.5 Inches
9.96 Feet x 9 Inches x 1.5 Inches

9.96 Feet x 12 Inches x 1.5 Inches

 

[MrBillSC]

Sometimes you need to check for more than "isnull" though I'm not sure why.
Try:
if not isnull({CO_ITEM_MASTER.DIM_5}) and
{CO_ITEM_MASTER.DIM_5} > "" then
output:=output + {CO_ITEM_MASTER.DIM_5} & " " & {CO_ITEM_MASTER_VAR.VAR_24} & " ";

MrBill

 

[synapsevampire]

Yeah, I assumed that you were correct in that it was a null, it isn't, try:

whileprintingrecords;
stringvar output:="";
if not(isnull({CO_ITEM_MASTER.DIM_5}))
and
trim({CO_ITEM_MASTER.DIM_5}) <> "" then
output:=output + {CO_ITEM_MASTER.DIM_5} & " "
...etc...

-k

 

[GaryCWL]

That was it. Thanks.