[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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.


0
cjtumlin
Asked:
cjtumlin
  • 3
  • 2
  • 2
1 Solution
 
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
 
mlmccCommented:
Did you try my formula?

Can you upload the report?

mlmcc
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now