Formula

[olushow]

I am writing the following formula against a SQL Server database, it works against an Oracle database.

It seems to give me values for either one of the other but not both. Do I have to rewrite it? Or is it a SQL Server thing.

if {HPD_Help_Desk.Status} in ["Resolved"] Then
datediff ("D", {HPD_Help_Desk.Last_Resolved_Date}, currentdatetime)
else
datediff ("D", {HPD_Help_Desk.Submit_Date}, currentdatetime)

What it does is that for records where the status is Resolved it is giving 0 Days active, even though logically that is not possible. even if it was a day, it should show at least a day or two, but definately not zero. This is being run against live records.

 

[lbass]

I think you need to check it for nulls like this:

if {HPD_Help_Desk.Status} <> "Resolved" and
isnull({HPD_Help_Desk.Last_Resolved_Date}) then
datediff ("d", {HPD_Help_Desk.Submit_Date}, currentdatetime) else
datediff ("d", {HPD_Help_Desk.Last_Resolved_Date}, currentdatetime)

-LB

 

[olushow]

That seemed to work.. Thanks.. BTW- what will be the syntax for the opposite of "isnull"?

For example - If I wanted to state, if Field.Value is not null

 

[GJParker]

Not IsNull()

HTH

Gary Parker
MIS Data Analyst
Manchester, England