Replacing a null date field with another date in a formula 1

[bcmom90]

I am using Crystal Reports 9. I have similar fields from 2 different tables.I want to replace any null Dates from the Purc_Order_Line table with the ones from the Purchase_Order table. Here's what I tried:

if DateValue ({PURC_ORDER_LINE.DESIRED_RECV_DATE})= DateValue (0,0,0 ) then
DateValue ({PURC_ORDER_LINE.DESIRED_RECV_DATE}) else
DateValue ({PURCHASE_ORDER.DESIRED_RECV_DATE})

I've tried every sort of Date function and nothing is happening. Has anyone been successful doing this and, if so, how is it done?

 

[lbass]

Why are you using datevalue? What are the datatypes of your two fields?

-LB

 

[bcmom90]

As I previously stated, I've tried several Date functions. These fields are definitely date-time fields. How would you write this formula? I would appreciate any assistance.

 

[synapsevampire]

Try:

if isnull({PURC_ORDER_LINE.DESIRED_RECV_DATE})
or
{PURC_ORDER_LINE.DESIRED_RECV_DATE} < cdate(1970,1,1)
then
DateValue ({PURC_ORDER_LINE.DESIRED_RECV_DATE})
else
DateValue ({PURCHASE_ORDER.DESIRED_RECV_DATE})

Thsi takes the approach of the field being either null, or before some date that would never appear in the field (adjust 1/1/1970 accordingly).

Many date fields are NEVER blank or 0/0/0, it depends on the database type, so remember that if you post about data, include the database type and connectivity used.

-k