Formula Not Pulling the Dates Correctly.

[jellybead]

Good Morning all,

We use Crystal 8.0 - Oracle.

Basically we're trying to have the report use the "ship date", unless it's blank. In that case, use the "Real ship date", otherwise use the "ship date".

Here is my formula:

if isnull ({CNX_DAILY_SHIPMENT\.SHIP_DATE})
then {CUSTOMER_ORDER_LINE\.REAL_SHIP_DATE}
ELSE ({CNX_DAILY_SHIPMENT\.SHIP_DATE})

The results are that it always uses the "real ship date".

Not sure where I'm going wrong here and wondered if one of you mighty formula guru's could help me out?

Thanks, in advance, for your help.

 

[lbass]

What is your record selection formula?

-LB

 

[Madawc]

Try a test report that does a simple display of the data, using the 'wizards', if Crystal 8 has them. Your formual ought to work.

Madawc Williams (East Anglia, UK)

 

[jellybead]

Hi lbass,
This is my record selection formula. I need to pull the report based on the "wanted delivery date", then show when it shipped, then calc to show that it was On Time, Late, or did not ship. This is in another field on the report.

{CUSTOMER_ORDER_LINE\.WANTED_DELIVERY_DATE}>= {?Select Data Range} and
{CUSTOMER_ORDER_LINE\.WANTED_DELIVERY_DATE}<= {?Select Data Range}

Does this help?

 

[Turkbear]

HI,
Use the Show Sql choice to see what query is actually going to Oracle.
(I am curious about your table name, by the way..I've never seen an Oracle table name ending in a \ )





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

 

[lbass]

My thought was that if shipdate was always null, that you might be causing that with your selection formula. I can't see that this is the case here, but I think your selection formula is odd. If the parameter is a range parameter, then it should be set up as:

{CUSTOMER_ORDER_LINE\.WANTED_DELIVERY_DATE}= {?Select Data Range}

It is probably currently working because you have "=" in both clauses. If it actually a discrete date and not a range, then your record selection formula is limiting the data to the one selected date only. I doubt that this is the problem though. How do you have your tables joined?

-LB

 

[jellybead]

Hey Lbass,

The ship date isn't always blank. In fact it was a recent addition set up as an Access front end and Oracle table. This means that the new table has much less data in it, than the oracle tables.

There are 6 tables, using left outer joins which worked fine with the requirements of the rest of the report.

I changed the range parameter, and it works fine too. Thanks for that.

???

 

[jellybead]

Want to thank everyone for their help.

I have located the problem and fix it, and the formula works perfectly.

Turns out that the person who set up the fields us LINE and LINE NO. My relationship was from LINE NO to LINE NO...seemed logical to me! .... oh no, he wanted me to use LINE but didn't say anything to me!

Just another day in wonderland!

Thanks again.