Null value when actually not null?

[IKONOS]

Greetings everyone!

I have created a report using CR 8.5, drawing information from a SQL database. This report is designed to give our business unit management the average total time it takes for the business unit to respond to an email by capturing the {submit date field} and the {response date field} and then subtracting the {response field} from the {submit field} to come up with the elapsed time the email has been idle before response to our customer. But, if there has been no response given, the field is left blank until there is one. In order to capture the truest numbers, I need to have the {response field} populated with the current date at the time the report is run, so that my formulas will work correctly.

Right now, if a business unit has let's say, 2 emails, but hasn't responded to either one of them (which means there's no [DATETIME] in the {response field}) then the sub-total field (where I subtract the {response} from the {submit}remains blank. But if I could insert the current DATETIME in this field, until there is a response, my report would be more substantial!

I have included and image of the report below, hoping it might help to visualize my posting.

Can anyone help?

Mark

 

[kenhamady]

What you need is a formula to use in your subtraction instead of the field:

If IsNull({DateTime.Field})
then CurrentDateTime
else {DateTime.Field} Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[IKONOS]

Thanks Ken, that wokrs perfectly! Except!

Now my subtraction formula doesn't work. How can I have both?

Mark

 

[IKONOS]

I need to be able to subtract the {Response_DT_TM} field and the CurrentDateTime from the {Create_DT_TM} field in order to come up with my 'elapsed time since recieved'

[M]

 

[kenhamady]

Copy and paste your 2 formulas.
What do you mean by "doesn't work"? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[IKONOS]

Well, it was very simple...{customer_response.response_dt_tm} - {customer_inquiry.create_dt_tm}. This gave me the elapsed time since the email was submitted (in days)until it was responded to... or not responded to. But, it only caught emails that had a response. This was done by creating a formula field called '@elapsed time since recieved.'

Now, by adding your formula to the formula field, it captures all emails with a response and places the current date-time if there is no response, but won't subtract the {create_DT_TM} from either the {response_DT_TM} or the CurrentDateTime.

Does that make sense?

 

[kenhamady]

Please copy and past all of the formulas into your next post.

I don't know what you mean by "it won't subtract". Does it give you an error message, print a zero, blow up? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[IKONOS]

Hey... sorry for the confusion!

In rearranging the formula fields I was able to obtain the outcome I was looking for!

Thank you so much for help!

Mark