cjtumlin
asked on
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_SE COND_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_FI RST_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.
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_SE
else
if
date({ZZCOODAT.DATE_SECOND
and not(date({ZZCOODAT.DATE_FI
else
if
date({ZZCOODAT.DATE_SECOND
and date({ZZCOODAT.DATE_FIRST_
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.
ASKER
No, I checked both the report that works and the one that doesn't and they are both set to "Exceptions for Nulls"
Did you try my formula?
Can you upload the report?
mlmcc
Can you upload the report?
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
AS it turns out, this was the problem. Thank You!
ASKER
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.
You're welcome. Glad I could help.
James
James
If the date fields are NULL I would use
If Not IsNull({ZZCOODAT.DATE_SECO
{ZZCOODAT.DATE_SECOND_NEG}
Else if Not isNull({ZZCOODAT.DATE_FIRS
{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