Need formula to remove unwanted dates 2

[thi123]

My report displays data including dates that does not exists on the database. I think these are the default dates and I don't want these to display on the report. I group the report based on the Order No. I have the formula that calculates number of days when the order was placed and the difference when the order was completed. I previously got help from lbass and the formula works. Now I need to remove these unwanted dates from displaying on my report.

My formula that calculates DateDiff:

if {#OrderRecord} = 1
then {FinDate}-{OrdDate}
else {FinDate}-{OrdDate})

Here's the Result:
OrdDate FinDate DateDiff
8/2/2002 11/11/02 69
8/2/2002 11/11/02 0
8/2/2002 1/1/1970 -11971
8/2/2002 1/1/1970 0


What I need to accomplish is to remove the 1/1/1970 dates and also don't display the DateDiff of 0 and -11971 since these are an incorrect value. Is there a simplier way to do this? Thanks again.

 

[dgillz]

Try a record selection formula of:

Not Isnull({FinDate})

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[synapsevampire]

The formula is bizarre:

if {#OrderRecord} = 1
then {FinDate}-{OrdDate}
else {FinDate}-{OrdDate}

The then and the else are identical, so it takes everything, and there's an extra parenthesis.

It sounds like you're using a SQL Server database, and the date returned isn't null, rather 1/1/1970 as a default, so try Report->Edit Selection Formula->Record and place:

isnull({table.FinDate})
or
{table.FinDate} <> cdate(1970,1,1)

This will remove those rows from the report.

If you still want the rows, and instead just want the formula to reflect the appropriate difference except in those instances of 1/1/1970, use:

if {#OrderRecord} = 1
and
not(isnull({table.FinDate}))
and
{table.FinDate} <> cdate(1970,1,1)
then
totext({FinDate}-{OrdDate})
else
""

You didn't state what you wanted to display if the date hasn't been updated.

-k

 

[thi123]

Thanks dgillz,

Can I add your recommendation on my formula or do I need to create a separate formula for Not IsNull({FinDate})?

 

[thi123]

Hi synapsevampire,

sorry, that is a typo. it is supposed to be:

if {#OrderRecord} = 1
then {FinDate}-{OrdDate}
else {FinDate}-{FinDate}

I will go ahead and try your suggestion. Thanks again.

 

[thi123]

Hi synapsevampire,

Have to correct the typo again. The formula is:

if {#OrderRecord} = 1
then {FinDate}-{OrdDate}
else {FinDate}-previous{OrdDate}

Duh!!! I'm such a DORK.....

 

[thi123]

synapsevampire, I used your suggestion with a slight modification but I am still getting the same result. The wrong values are still showing... What I am missing????

if {#OrderRecord} = 1
and
not(isnull({table.FinDate}))
and
{table.FinDate} <> cdate(1970,1,1)
then
totext({table.FinDate}-{table.OrdDate})
else
totext({table.FinDate}-previous(table.FinDate})

 

[thi123]

Thanks synapsevampire,

I got it. The formula is working.