outer join on date?

[Sambo8]

Hi All,

I'm using Crystal 8.5 ODBC.

I'm trying to outer join to a table where they may be no data, and therefore it is excluding the data from my first table. I've outer joined on 1 field USERCODE. But I have a parameter of date that I select both tables = {?date} I want to show info from the first table even if the second table doesn't meet the = {?date) how do I allow for nulls here?

Many thanks in advance.

Sam

 

[lbass]

Try a left join from the table that includes all the records, let's call it Table1, to the second table. Then use a record selection that only references the first table:

{Table1.date} = {?date}

Then create a formula to display only those records in the second table that meet the date criteria:

if isnull({table2.date}) or
{table2.date} <> {?date} then date(0,0,0) else
{table2.date}

Or instead you can try a record selection formula of:

{Table1.date} = {?date} and
(
isnull({table2.date}) or
{table2.date} = {?date}
)

Although this could exclude records from the first table when there are only dates in the second table that don't meet your parameter criterion.

-LB