Why doesn't this formula work as I expect? 1

[AyJayEL]

I'm trying to write a formula to show the number of days either between a start date and an end date or where the end date is blank, to use today's date. It will only show where there is an end date and shows blank where there is no end date. Can anyone tell me what I'm doing wrong? See below.

Local DateTimeVar d1 := {VIEW_EXCL_SCHEDULE.START_DATE};
Local DateTimeVar d2 := {VIEW_EXCL_SCHEDULE.END_DATE};
Local DateTimeVar d3 := currentdate;

If (isnull({VIEW_EXCL_SCHEDULE.END_DATE}))
then
(DateDiff ("d", d1, d3) - DateDiff ("ww", d1, d3, crSaturday) -DateDiff("ww", d1, d3, crSunday))
else
(DateDiff ("d", d1, d2) - DateDiff ("ww", d1, d2, crSaturday) -DateDiff("ww", d1, d2, crSunday))


Learn something new every day *:->*
AyJayEl

 

[dgillz]

No need for variables at all, just try this:

If isnull({VIEW_EXCL_SCHEDULE.END_DATE})then  DateDiff ("d", {VIEW_EXCL_SCHEDULE.START_DATE}, CurrentDate) 
else

DateDiff ("d", {VIEW_EXCL_SCHEDULE.START_DATE},{VIEW_EXCL_SCHEDULE.END_DATE})

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

http://www.gainfocus.biz

 

[AyJayEL]

Great, got rid of the variables but I the formula is still not working properly. I see a calculation for when there is an end date but it shows null when there is no end date.

Learn something new every day *:->*
AyJayEl

 

[lbass]

Try going to file->report options and see if you have "convert nulls to default values" checked, and try unchecking it. Otherwise you could try a test like:

if isnull({table.date}) or
{table.date} = date(0,0,0) then...

dgillz' formula left out the check for Saturdays and Sundays, which you would have to build back in.

-LB

 

[AyJayEL]

I had already tried the 'convert nulls to default' turning them on and off to no avail! And I had spotted the lack of Saturdays and Sundays thanks.

When I try your little formula using 1 after then it displays 1 when the end date is blank and 0 when it has a value.



Learn something new every day *:->*
AyJayEl

 

[AyJayEL]

How weird, suddenly it's started working! Thanks for your help chaps. Much appreciated.

Learn something new every day *:->*
AyJayEl