Selection formula causes .rpt to close down

[ajaeger]

I'm using Crystal 8.5 and have the following formula as my record selection. When I run the report, it shuts down before prompting me for the password. If I delete the state part in bold, it will run fine.

(isnull({Activity.THRU_DATE}) or {Activity.THRU_DATE}>=CurrentDate) and
{Activity.PRODUCT_CODE} = "COMMITTEE/ST_STRAT_TM" and
{Name_Address.PURPOSE} = "P.O. Address" and
(
if "AL" = {?STATE_CODE} then {STATE_REPS.ALABAMA} or
if "AK" = {?STATE_CODE} then {STATE_REPS.ALASKA}
)

There must be something funky that I'm overlooking in those last few lines. I don't get any errors found when I check the formula...

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[IdoMillet]

That last part doesn't return a logical True or False.
Change the logic so it does.

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IanWaterman]

YOu can not use or in an If then statement try.

(
if "AL" = {?STATE_CODE} then {STATE_REPS.ALABAMA} else
if "AK" = {?STATE_CODE} then {STATE_REPS.ALASKA}
)

Ian

 

[IanWaterman]

Sorry Ido is correct too
YOu need {STATE_REPS.ALABAMA} to eqal something eg

(
if "AL" = {?STATE_CODE} then {STATE_REPS.ALABAMA} = 'Y' else
if "AK" = {?STATE_CODE} then {STATE_REPS.ALASKA} = 'Y'
)

 

[ajaeger]

Thanks for the help. {?STATE_CODE} is a multi-select parameter - for example it could be "AL,NY,NJ". I need to select all records that have the checkboxes in the State_Reps table checked for those three states - all records where State_Reps.ALABAMA, State_Reps.NEW_JERSEY and State_Reps.NEW_YORK are checked.

What I was able to get working was:

({Name_Address.PURPOSE} = "P.O. Address" and
(isnull({Activity.THRU_DATE}) or {Activity.THRU_DATE}>=CurrentDate) and
{Activity.PRODUCT_CODE} = "COMMITTEE/ST_STRAT_TM" and
if "AL" = {?STATE_CODE} then {STATE_REPS.ALABAMA})
or
({Name_Address.PURPOSE} = "P.O. Address" and
(isnull({Activity.THRU_DATE}) or {Activity.THRU_DATE}>=CurrentDate) and
{Activity.PRODUCT_CODE} = "COMMITTEE/ST_STRAT_TM" and
if "AK" = {?STATE_CODE} then {STATE_REPS.ALASKA})
or...

Had to do for all 50 states, but got it working.


Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[IanWaterman]

Can see no benefit of having these fields

50 x {STATE_REPS.STATE}

You would be be better off having a single StateCode field. Ask DBA to redesign or just shoot him/her.

Ian

 

[kskid]

How about

({Name_Address.PURPOSE} = "P.O. Address" and
(isnull({Activity.THRU_DATE}) or {Activity.THRU_DATE}>=CurrentDate) and
{Activity.PRODUCT_CODE} = "COMMITTEE/ST_STRAT_TM" and
(
Select {?StateCode}
Case "AL":
{STATE_REPS.ALABAMA}
Case "AK":
{STATE_REPS.ALASKA}
.
.
.
Case "WY":
{STATE_REPS.WYOMING}
)