Concatenating 3 fields when one field has no value

[jack91]

Hi,

I have 3 fields that i need to concatenate.

Issue:

When one field has *no value but the other 2 fields **have value, and when i concatenate none of the fields which do have value do not show up.

Is there a way to fix this?
I would like to show the fields as

Good/Bad/Ugly -- basically each filed separated by "/"

Thanks
Jk

 

[jack91]

I am doing a search on tek-tips and found this:

http://www.tek-tips.com/viewthread.cfm?qid=1468064&page=7

 

[jack91]

I forgot to mention:

I am using crystal version 10

 

[ianoctdec]

Hi,

Create a formula for each field, to take care of nulls:

@field1
if isnull(table.field1) then '' else table.field1

Same for @field2 and @field3

Use these formulas in concatenation.


Dana

 

[jack91]

something like this? - this is from the link i mentioned earlier:


StringVar addr := "";

If IsNull({ADDRESSES.STREET_NUMBER}) Then
addr := addr
Else
addr := addr + ToText({ADDRESSES.STREET_NUMBER});

If IsNull({ADDRESSES.STREET_NAME}) Then
addr := addr
Else
addr := TrimLeft(addr + " " + {ADDRESSES.STREET_NAME});

if isnull({ADDRESSES.STREET_CD_STREET_TYPE_CODE}) then
addr := addr
else addr := addr + " " + {ADDRESSES.STREET_CD_STREET_TYPE_CODE};

trim(ADDR)

 

[jack91]

I tried using:

if isnull({FIELD 1})then '' else {FIELD 1}
&" "&
if isnull({FIELD 2})then '' else {FIELD 2}
&" "&
if isnull({FIELD 3})then '' else {FIELD 3}

i don't think it worked

TIA
Jk

 

[Turkbear]

Hi,
Don't combine the Field formulas:
Like ianoctdec posted use 3 formulas and concatenate them into a new field...






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

 

[jack91]

Turkbear: I am creating the formulas for the field first.

One of my fields is telling me "a string is required here"

if isnull ({FIELD 2}) then '' else {FIELD 2}----> this field is telling me that "a string is required here"

the other 2 formulas are fine.

what could be the problem here?

Jk

 

[jack91]

Regarding my last comment:

I checked the value of the field, its "int". Is there no way to create a formula (see below) without going into the database and changing the "int" to "vachar"

if isnull ({FIELD 2}) then '' else {FIELD 2}----> this field is telling me that "a string is required here"


Thanks
Jk

 

[Turkbear]

Hi,
Try:

If IsNull(ToText({FIELD 2})) then "" Else ToText({FIELD 2})

All parts of the concatenation need to be the same type so the conversion to text is needed for an integer field.





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

 

[jack91]

When i replace the field 2 with my own field, it gives error saying "a field is required here

 

[jack91]

I wonder if something like this would work

if isnull ({FIELD 2}) then '' else CStr (FIELD 2})

 

[jack91]

anyone? please advise on my last few comments

TIA
Jk

 

[dunlop1975]

Can you paste the exact line from your formula? Turkbear's syntax should have been fine.

The a "field is required here" message is usually a syntax error

 

[jack91]

If IsNull(ToText({myfield})) then "" Else ToText({myfield})

 

[jack91]

If IsNull(ToText({v_monthly_finance_sheet.misc_dept})) then "" Else ToText({v_monthly_finance_sheet.misc_dept})

 

[dunlop1975]

I copied the syntax to a report with an int field and received the same message, I changed to :

If IsNull({v_monthly_finance_sheet.misc_dept}) then "" Else ToText({v_monthly_finance_sheet.misc_dept})

and it worked fine.

I see however that this syntax did not work for you a couple of posts above. Can you copy this exact formula syntax at let us know if the error re-occurs?

 

[jack91]

dunlop1975:

If IsNull({v_monthly_finance_sheet.misc_dept}) then "" Else ToText({v_monthly_finance_sheet.misc_dept})
This works .

BUT

if the field value was 0 then it shows up as 0.00, is there any way to just show as 0?

THANKS
Jk

 

[dunlop1975]

If IsNull({v_monthly_finance_sheet.misc_dept}) then "" Else ToText({v_monthly_finance_sheet.misc_dept},0)

 

[jack91]

dunlop1975:

THANK YOU!1 THANK YOU!! THANK YOU!!