record selection statement ignoring date criteria

[rorymo]

Sql Server 2000
Crystal Reports 2008 v 12.2.0.290

When I run a report that has this record selection statement, all of the expected data is returned except
the date statement part is being ignored. Instead of finding records that have open dates beginning 10/1/2009, it goes back to 10/1/2008
(which is as far as the db goes back).I looked at the sql statement and it does not even have the date part mentioned.
(BTW, we have to use formulas for dates to account for daylight savings time changes.)
I thought that using parenthesis would help but it acts the same way, with or without them.
I have tried putting the statement in a different location in the selection statement, but it doesn’t help.
What I have found is that when I take out all of the “if then” statements, it will work, however we need to have those because we have to create “artificial groups” thru formulas and need to have a parameter to select those groups.I can’t think of anything else to try.
Any help would be appreciated.
Thanks in advance,
Rory

(
{@Open Date}
in
DateTimeValue (2009,10,01,00,00,00) to currentdatetime
)
and
(
{chg.active_flag} = 0
)
and not
(
{@Close Date} < {@Est Compl Date}
)
and
(
if {?Group selection} = "IT Service Support" then
{@IT Service Support}
)
or
(
if {?Group selection} = "Core Systems" then
{@Core Systems}
)
or
(
if {?Group selection} = "Infrastructure" then
{@Infrastructure}
)
or
(
if {?Group selection} = "Business Systems" then
{@Business Systems}
)
or
(
if {?Group selection} = "Applications" then
{@Applications}
)
or
(
if {?Group selection} = "RD1-VISN 18" then
{@RD1-VISN 18}
)
or
(if {?Group selection} = "RD1-VISN 19" then
{@RD1-VISN 19}
)
or
(
if {?Group selection} = "RD1-VISN 20" then
{@RD1-VISN 20}
)
or
(
if {?Group selection} = "RD1-VISN 21" then
{@RD1-VISN 21}
)
or
(
if {?Group selection} = "RD1-VISN 22" then
{@RD1-VISN 22}
)
or
(
if {?Group selection} = "Region 1 CTO" then
{@Region 1 CTO}
)
or
(
if {?Group selection} = "Region 1 Director" then
{@Region 1 Director}
)
or
(
if {?Group selection} = "Region 1 Regional Governance Board" then
{@Region 1 Regional Governance Board}
)

 

[IanWaterman]

All your ORs ignore the date range.

YOu have to decide what your Ors are relative too and wrap them in () and link to the date range with an AND

Ian

 

[rorymo]

Thank you Ian!
I would have never thought of that.
I appreciate your fast reply.
Have a good weekend.
Rory

 

[rorymo]

It worked great!
Thanks!

 

[fisheromacse]

you need to logically group your parenthesis.

it is selecting the date and ... and ... or ... or ...

below i copy pasted your formula and added "()" before the "({@Open Date}", after the "currentdatetime)" before "(if {?Group selection}" and after the last paren of your formula "{@Region 1 Regional Governance Board})".




(
(
{@Open Date}
in
DateTimeValue (2009,10,01,00,00,00) to currentdatetime
)
)
and
(
{chg.active_flag} = 0
)
and not
(
{@Close Date} < {@Est Compl Date}
)
AND
(
(
if {?Group selection} = "IT Service Support" then
{@IT Service Support}
)
or
(
if {?Group selection} = "Core Systems" then
{@Core Systems}
)
or
(
if {?Group selection} = "Infrastructure" then
{@Infrastructure}
)
or
(
if {?Group selection} = "Business Systems" then
{@Business Systems}
)
or
(
if {?Group selection} = "Applications" then
{@Applications}
)
or
(
if {?Group selection} = "RD1-VISN 18" then
{@RD1-VISN 18}
)
or
(if {?Group selection} = "RD1-VISN 19" then
{@RD1-VISN 19}
)
or
(
if {?Group selection} = "RD1-VISN 20" then
{@RD1-VISN 20}
)
or
(
if {?Group selection} = "RD1-VISN 21" then
{@RD1-VISN 21}
)
or
(
if {?Group selection} = "RD1-VISN 22" then
{@RD1-VISN 22}
)
or
(
if {?Group selection} = "Region 1 CTO" then
{@Region 1 CTO}
)
or
(
if {?Group selection} = "Region 1 Director" then
{@Region 1 Director}
)
or
(
if {?Group selection} = "Region 1 Regional Governance Board" then
{@Region 1 Regional Governance Board}
)
)

 

[fisheromacse]

doh! sorry got distracted, walked away, came back hit post....and Ian already beat me to it