# 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.

###### Who is Participating?

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

Commented:
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

Author 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

Commented:
Did you try my formula?

mlmcc
0

Author Commented:
AS it turns out, this was the problem. Thank You!
0

Author 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

Commented:
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.