Totalling columns (formulas)

[lmorgan]

Perhaps I've been looking at this too long, too hard. I know the solution must be simple. I'm working in Crystal 8.0.

I created 3 formulas to calculate the datediff between start and completion of 3 separate processes (and returned in days format)and placed in the report.

What I need to do now is add the results of the three columns representing the total number of days that were returned (some are blank, as the process never occurred)into a "Totals" column.

To me the formula seems simple but no data is returned in the Total column at all. Is it possible that the reason is because some of the results of my initial formulas are blank?

An example of the formula(s) I wrote to calculate the number of days is:

DateDiff ("d",{bankruptcy.date_filed}, {bankruptcy.released_date}).

If that is the reason why it will not add the columns, how can I get around this? (somehow creating an If statement to return a zero for number of days? If so, need help with that also).

Thanks so much for any and all help!
Lisa

 

[paulmarr]

Imorgan,

I think your problem lies in trying to add values that don't exist. Try amending your initial formulas to add zero if process date is null. Use the IsNull() function - which returns true or false for a date value.

Hope this helps

Cheers,

paulmarr@arcom.com.au

 

[lmorgan]

Paulmarr -

Thank you so much for your response. I thought that might have been what the problem is, but am having a difficult time in getting the IsNull formula written properly (I've only used Crystal for about 1.5 months so forgive me if this is an easy fix).

Although I'm referencing two separate date tables, neither were placed in the details column. From these two tables I wrote the formula:

DateDiff ("d",{bankruptcy.date_filed}, {bankruptcy.released_date})

which returns a numeric value. What sort of IsNull statement should be written to return the value of zero if there is no data? I've tried an "If IsNull" type of forumula but I think I'm messing up the sequencing.

Again my thanks, and I look forward to your response,
Lisa

 

[kenhamady]

I am not sure which date would be null (or possibly both) so try:

If IsNull({date1}) or IsNull(Date2)
then 0
else DateDiff ("d",{bankruptcy.date_filed}, {bankruptcy.released_date})
Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[paulmarr]

Imorgan,

Yes, I think Ken is correct. Just remember that IsNull() is a boolean function, which returns True/False.

Cheers,

paulmarr@arcom.com.au