Isnull in 8.5 1

[Kathyrlcrs]

I am having to take a report from 9.0 and create it in 8.5.
I am using an access database.

I had this formula working in 9.0:

If Not IsNull({Brokerage_Offer.ReducedAmount})and ({Brokerage_Offer.ReducedAmount}) > 0 then
{Brokerage_Offer.ReducedAmount} else
{Brokerage.ListPrice}

I am using an Employee, Company and Brokerage table in addition to the Brokerage offer table.

For some reason this formula will not work in 8.5. It seems not to work when there is no Brokerage_offer record.
If there is no brokerage offer record it does not print any of that information for that employee.
How can I rewrite this formula to check for the no record.
If there is a Brokerage Offer record then I need to print that info, if there is not a Brokerage offer record then I need to print the data from the brokerage table.

The Isnull is not taking care of that in 8.5. It did in 9.0.
Any suggestions in how I can rewrite this formula?

Thanks!!

 

[lyanch]

Try:

If IsNull({Brokerage_Offer.ReducedAmount}) or({Brokerage_Offer.ReducedAmount}) <= 0 then
{Brokerage.ListPrice}
else
{Brokerage_Offer.ReducedAmount}

Lisa

 

[Kathyrlcrs]

I tried the IsNull formula. It still doesn't work.

What is happening is that I have Brokerage records. Some of these records have a Borkerage_Offer record Linked to it and some don't. If there is no Brokerage_offer Record then it doesn't print any of the Brokerage information either.

In 8.5 how do I check if there is a Brokerage_Offer record. THe IsNull works in 9.0. Why not in 8.5?

 

[synapsevampire]

It sounds like you have the joins incorrect, or your putting something in the record selection formula which is specifically omitting those rows.

Check the links in the tables to assure that you are using a Left Outer join.

Lisa's formula should be fine then.

Your formula seems Ok too, but you might further qualify it with parentheticals for the NOT clause:

If Not (IsNull({Brokerage_Offer.ReducedAmount}))
and
({Brokerage_Offer.ReducedAmount}) > 0 then
{Brokerage_Offer.ReducedAmount}
else
{Brokerage.ListPrice}

Another trick in CR 8.5 is to use the More Data Sources->ADO and paste in the SQL from your CR 9 report.

-k

 

[Kathyrlcrs]

Thank you k

I checked the links but forgot to check each specific table.
Thank you so much.
I finally got it to work.

Kathy

 

[synapsevampire]

Glad it worked.

When recreating reports, try using the Database->Show SQL Query to look for differences in the SQL.

-k

 

[Kathyrlcrs]

Thank You so much!

Kathy

 

[LLowrance]

Sounds like your joins need to be addressed. Perhaps a left outer join rather than equal join.