Solved

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

Posted on 2011-10-14
215 Views
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.

0
Question by:cjtumlin

LVL 100

Expert Comment

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 Comment

No, I checked both the report that works and the one that doesn't and they are both set to "Exceptions for Nulls"
0

LVL 100

Expert Comment

Did you try my formula?

mlmcc
0

LVL 34

Accepted Solution

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

Author Closing Comment

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

Author Comment

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

LVL 34

Expert Comment

You're welcome.  Glad I could help.

James
0

## Featured Post

### Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.