Record Selection Formula Problems

[agorjest]

Hello,
I'm using CR2008, with ODBC to a remote SQL server.

Looking to pull service workorder records based on user chosen start and end date parameters, but depending on the status of a workorder, the "date" of the workorder can be drawn from 1 of 2 different fields: Status Date or Actual Completion Date. If the "Status" of the workorder says "Completed", then there is a date value stamped in the Actual Completion Date field. If the workorder status is anything other than "Completed", the Actual Completion Date is a null field. For "Completed" workorders, I want the select expert to evaluate the date of the workorder based on the Actual Completion Date field. If the workorder is not "Completed", I want the select expert to evaluate the date of the workorder based on the Status Date field.
Here is the part of my selection formula that I'm using to try and accomplish this:

(if not (isnull ({workorder.actfinish})) then {workorder.actfinish} >= {?Enter Start Date} and {workorder.actfinish} <= {?Enter End Date} else
{workorder.statusdate}>= {?Enter Start Date} and
{workorder.statusdate}<= {?Enter End Date})

Also tried this one:

(if {workorder.status} = "COMP" then
{workorder.actfinish} >= {?Enter Start Date} and {workorder.actfinish} <= {?Enter End Date} else
{workorder.statusdate}>= {?Enter Start Date} and
{workorder.statusdate}<= {?Enter End Date})


Neither of these are returning any records. Any ideas on what I'm missing or if there is a better way to do it?

Thanks!

 

[CoSpringsGuy]

({workorder.status} = "COMP"
and
{workorder.actfinish} >= {?Enter Start Date}
{workorder.actfinish}<= {?Enter End Date})
or
({workorder.status} <> "COMP"
and
{workorder.statusdate}>= {?Enter Start Date} and
{workorder.statusdate}<= {?Enter End Date})

??



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

I would use:
(
(
(
isnull({workorder.actfinish}) or
{workorder.status} <> "COMP"
) and
{workorder.statusdate}>= {?Enter Start Date} and
{workorder.statusdate}<= {?Enter End Date}
) or
(
{workorder.status} = "COMP" and
{workorder.actfinish} >= {?Enter Start Date}
{workorder.actfinish}<= {?Enter End Date}
)
)

-LB

 

[agorjest]

Got it to work. Thanks for the suggestions!