Conditional Working Day Function Returns Zero Problem 1

[Mav3000]

Hi All,

In our database we have the option of specifying if a date is 'not applicable' on a record by selecting 'n/a' on the database's front-end. As the date field cannot store a string, the database uses the date '1901-12-31' to store this special value. Whenever the database sees this date, is shows 'n/a' instead to the user.

However, this is causing me a problem in Crystal Reports:

I have two dates. I want to show the difference in Working Days between the two. to do this, I use the Ken Hamady technique (

http://www.kenhamady.com/form01.shtml).

The problem is, is that I only want to calculate the difference if both the date fields have a value higher than '1990,01,01' - to eliminate both empty fields and 'n/a' fields.

When I run the following code, on fields that contain '1901,12,31' I get zero - when in fact I should get nothing.

-------------

WhileReadingRecords;

Local DateVar Start := date({dtblDMC2092Jobs.D_Initial_Notification}); // place your Starting Date here
Local DateVar End := date({dtblDMC2092Jobs.D_MSV}); // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

if {dtblDMC2092Jobs.D_Initial_Notification} > date(1990,01,01)

and {dtblDMC2092Jobs.D_MSV} > date(1990,01,01)

then Weeks + Days - Hol -1

-------

Returning Zero is a problem because when I try to show the average of the differences for all records, it picks up on the zero and throws the average out.

How can I tell the formula to return 'nothing' instead of zero if either of the dates are less than '1990,01,01'?

Thanks,

Richard

 

[lbass]

Why not exclude those dates from the report in the record selection formula?

Or you could use a running total for the average where you specify in the evaluation section in a formula:

{dtblDMC2092Jobs.D_Initial_Notification} > date(1990,01,01)and
{dtblDMC2092Jobs.D_MSV} > date(1990,01,01)

-LB

 

[Mav3000]

Hi Lbass,

I have a lot of fields on the report, so am put off creating a lot of selection filters for each field (all of which have the 'n/a' capability).

However, the Running Total idea works a treat. I can filter out the dates I want and add up all the others per group.

Thanks very much for your continued help,

Richard