Compare 3 date fields in a single record and return the maximum

I have a database that includes a sales order table with three possible dates for each record; Date-Required, First-Negotiated-Date and Second-Negotiated-Date.  If an order goes out as scheduled, the date-required will be the only one of the three that has a value in it. If a customer calls to ask for a different delivery date, the date-required remains the same and the first-negotiated date gets the a value, while the second-negotiated date remains null. If they call and change it again, all three fields will have a value.

My problem is that I have a formula to return the latest of these dates but it only works in certain reports.  The formula is:

if
not(date({ZZCOODAT.DATE_SECOND_NEG}) = Date (0000,00,00)) then {ZZCOODAT.DATE_SECOND_NEG}
else
if
date({ZZCOODAT.DATE_SECOND_NEG}) = Date (0000,00,00)
    and not(date({ZZCOODAT.DATE_FIRST_NEG}) = Date (0000,00,00)) then {ZZCOODAT.DATE_FIRST_NEG}
else
if
date({ZZCOODAT.DATE_SECOND_NEG}) = Date (0000,00,00)
    and date({ZZCOODAT.DATE_FIRST_NEG}) = Date (0000,00,00)
then
{ZZCOODAT.DATE_REQ}

I have tried using 'isnull' instead of '= Date (0000,00,00)" as well as putting the datetime format in there.

In one report, I get whichever date I need, in another report which uses the exact same tables, with the exact same links, this formula will only return the second negotiated date, while in any records that do not have a second negotiated date the formula comes out blank.

I have even copied the formula from the report that works into the one that doesn't to make sure I didn't fat finger something but it still will not work.

Is there a better way to compare three dates in a single record where one or more may be null and return the maximum of the three?

I am using Crystal Reports XI SP2.


cjtumlinAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
You could also check File > "Report Options" and see if one report has "Convert Database NULL Values to Default" checked and the other one doesn't.

 James
0
 
mlmccCommented:
Do you want the maximum date the last negotiated date?

If the date fields are NULL I would use

If Not IsNull({ZZCOODAT.DATE_SECOND_NEG})  then
    {ZZCOODAT.DATE_SECOND_NEG}
Else if Not isNull({ZZCOODAT.DATE_FIRST_NEG}) then
    {ZZCOODAT.DATE_FIRST_NEG}
Else
    {ZZCOODAT.DATE_REQ}


You might also check if the formula that doesn't work has the DEFAULT VALUE FOR NULL option set in the formula editor

mlmcc
0
 
cjtumlinAuthor Commented:
No, I checked both the report that works and the one that doesn't and they are both set to "Exceptions for Nulls"
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
mlmccCommented:
Did you try my formula?

Can you upload the report?

mlmcc
0
 
cjtumlinAuthor Commented:
AS it turns out, this was the problem. Thank You!
0
 
cjtumlinAuthor Commented:
Sorry, that wasn't very clear, the problem was that the "Convert Database NULL Values to Default" option was only checked on one fo the reports.  Once I checked it on the other, they worked fine.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.