convert no value to a value when a left outer join left me with null

[mdtimo]

I have two subreports that I need to link to a master report.

The field the expenses subreport links to the master is a user defined field in a seperate table from the actual expenses. Often this field is null and I use a left outer join to link the expense table to the expenses user defined table and it works fine.

But the work table also has a user defined field which is on the record with the work data and not in a seperate table as in the expense table.

The work table never has a null value but has a zero value where the expense user defined table has no record, mind you I still have the expense record just no user defined record at all.

What I want to do is link the two subreports into the master report but when there is no user defined record on the expense subreport I need a formula to give me zero if the link couldn't be made and the value if the link could. Then I could link the two subreports together on the master report using the user defined value on the work and the formula on the expenses table.

But the following formula doesn't work

if isnull({expense.udef}) then "0" else {expense.udef}

I get no data, I assume because the field isn't null but nonexistent.

 

[chelseatech]

NULL is empty data.

Check you haven't set "Convert NULL to default"
Check you've got records in your subreport. NULL is a field value for a record. If no records then do a summary and check IsNULL(Count({table.field}))
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Roadkill150]

If the convert null to default doesn't work, another workaround would be:
if length({expense.udef})<1 then &quot;0&quot; else {expense.udef}

cheers,
-Bruce
Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com