Compare 2 dates and display the earliest 1

[nb4884]

Hi All,

I need to set a formula for dates.

I have stage field as 0,1,2,3 . and each stage has a date. A project can have each stage multiple times.

For Ex Project A has stage 0 Date 01/01/01
Project A has stage 0 Date 01/01/02
Project A has stage 1 Date 01/01/03 and so on.

I need to write a formula for Stage 0 and pick the earliest date.

@Date = if ({Query1.Stage}) = '0' the ({Query1.Date}).

Then placed @Date in details and > insert summary > maximum at group level.

Now if I add date field to Sort in Explorer and select Descending, it shows me the highest date i.e 01/01/02 but if I select as ascending it shows blank.

I wanted to see the earliest date in this for stage = 0
which is 01/01/01

Pls suggest

 

[Madawc]

Group by project. Create a summary total, minimum date.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

My answer assumes that the different record types use the same date field. If not, you'll need to create a formula field to select the 'relevant date'. Note that missing dates are usually nulls, and you need to use IsNull first because otherwise the null will stop the formula with no output.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Create a formula {@null} by opening a new formula and saving it without entering anything. Then change your formula to:

if {Query1.Stage) = '0' then
{Query1.Date} else
date({@null})

Then insert a minimum on this formula.

-LB

 

[nb4884]

Thanks!

LB- I tried the formula , it gave me a bad date format error. My date is a datetime field

I then added totext for date and took off the date in front of @null.

I still see a blank in report. Minimum date ddo not display

 

[lbass]

Make sure that in file->report options you have NOT checked "convert nulls to default values".

If the date is a datetime, the the following should work:

if {Query1.Stage) = '0' then
{Query1.Date} else
datetime({@null})

Otherwise, you could change the formula to:

if {Query1.Stage) = '0' then
{Query1.Date} else
datetime(9999,9,9,0,0,0)

Then insert a minimum on this. Or could either stage or the datetime be null to begin with???

-LB

 

[nb4884]

Thanks LB

The first sentence was all about it"Make sure that in file->report options you have NOT checked "convert nulls to default values".

I unclicked the boxes and same formula worked