Matching up two tables with Null values.

[ericfmyers]

I'm using Crystal Reports 2008 and I'm having a problem with Null values. I have two tables that are being compared in the report to return an account number. If there is no account number setup yet then I would like for it to return a message stating "Please add Account #".

The first table contains the records of the invoice and is called Books_billing. The second table is named Cust_DB. They are being linked like so:

{Books_billing.Company Name} = {Cust_DB.NAMECUST}

The formula I'm trying to put into the report is:

If IsNull({Cust_DB.AP-TOLAS-6-4}) then "Please add Account #" else {Cust_DB.AP-TOLAS-6-4}

This formula works most of the time but sometimes there is no row in the table that matches for {Cust_DB.NAMECUST}, so then the entire line is dropped.

And Yes, I do have the options checked for Convert Database Null Values to Default and Convert Other Null Values to Default.

Any help is appreciated. Thanks.

 

[Madawc]

Try a left-outer link between Books_billing and Cust_DB.

If you have a record selection for Cust_DB, this will have to allow for nulls in selection field.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

First go to report options and uncheck the two places where it says "convert nulls to", and then use a left join FROM the books_billing table TO the Customer table, with no selection criteria on the Customer table. Then your formula should work. This assumes that the billing table always has all records, but the customer table does not necessarily.

-LB

 

[ericfmyers]

That worked! Thanks!