Help with If Formula

[dnayana]

I need assistance with writing a formula.

Requirement: Display a "1" for each record that have a Status of "Work In Progress" and have a Date Diff of 10-59 days.

If I do the following,

if {CHG_Task.Status}='WorkInProgress' then 1

then the one displays; but as soon as I add the other clause to it,

if DateDiff ("d",{CHG_Task.Create_date},{?EndDate}) in 10 to 59 and {CHG_Task.Status}='WorkInProgress' then 1

or

if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate}) in 10 to 59 and {CHG_Task.Status}="WorkInProgress") then 1

the number doesn't display.

Looks to be really simple, but my output is not displaying the way it should.

Thanks in advance to anyone who may be of assistance.

 

[hilfy]

What version of Crystal are you using?

Try adding some additional parentheses:

if ((DateDiff ("d",{CHG_Task.Create_date},{?EndDate}) in 10 to 59( and ({CHG_Task.Status}="WorkInProgress")) then 1

You also may have to break it down into two If statements:

if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate}) in 10 to 59 then 
  if {CHG_Task.Status}="WorkInProgress") then 1

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[dnayana]

Hilfy,

First, thank you for responding.

I'm using version 9.2.

I tried both of your codes, but modified the first with

if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate}) in 10 to 59) then
if {CHG_Task.Status}="WorkInProgress" then 1

and the second with

if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate}) in 10 to 59) then
if {CHG_Task.Status}="WorkInProgress" then 1

because I received the "The ) is missing" error with the both of them.

BTW, my record selection formula is

{CHG_Task.Status} <> "Closed" and
{CHG_Task.Type} = {?System} and
{CHG_Task.Phase} = {?Stage} and
{CHG_Task.Create_date}>={?StartDate} and
{CHG_Task.Create_date}<={?EndDate}

which, I don't think should have anything to do with it.

 

[lbass]

I think you need to lay out the fields in your detail section to see how they are displaying. Try creating just the datediff formula separately. You might have nulls in one or more fields, or your datediff formula might have negative results. You also might need to verify that "WorkInProgress" is always entered in the same way. Otherwise, there is nothing I see wrong with your original attempted formula.

-LB

 

[dnayana]

Hi Lbass!

I've already been doing what you have suggested (i.e. laying out the fields in the detail section); that's how I'm seeing that my number isn't displaying because I'm going to eventually hide the detail section.) I also have both conditions separately and they both worked individually.

I'll test for nulls in the Status field and also view all values to ensure they are always entered in the same way to see if that could be the problem.