Problems with Parameters using multiple if statements

[rorymo]

Crystal Reports XI (BOXI)
Sql Server 2000

Hi,
I have a report that needs to use multiple pick parameters and I cannot get it to work correctly. It is not seeing all of the selection criteria, it "falls through" after the first selection (History)- see below - and ignores all of the other picks that are entered in the selection boxes.
The only way that I can get the syntax to check correctly is to use "or" but I am not sure that the logic is right.

What I need is for the first statement({prob_ctg.sym} like "*hdr*") to make an initial record selection and then
return the records based on the additional parameters.
The customer wants to select a value from all of the choices, so each parameter will have a selection criteria filled in.

I know that sometimes the order of the statements makes a difference in how Crystal parses through it, but I have tried many different ways and still cannot get good results.

This is what my record selection statement looks like:

{prob_ctg.sym} like "*hdr*" and
if {?Select One}= "History" then {@Range Month}=false
or
if {?Select One}= "Last Full Month" then ({@Range Month}=true and {@Last Full Month}=true)
or
if {?Select One}= "Last Full Week" then ({@Range Week}=true and {@Last Full Week}=true)
or
(if {?Priority} = "All" then True else {@Priority} = {?Priority})
or
(if {?Status} = 2 then True else {call_req.active_flag} = {?Status})
or
(if {?Assignee} = " " then True else {ctct_1.c_last_name} like ""+{?Assignee}+"*")

I hope this is not too confusing..
I appreciate any help on this,
Thanks in advance,
Rory

 

[synapsevampire]

It doesn't make sense anyway, selection criteria should be against fields in the database, not formulas. The point is to filter rows by passing SQL to the database.

And we don't know what's in your formulas, you need to take the time to post those as well, although in this case I think you want to eliminate them.

In general you'll want something like:

(
prob_ctg.sym} like "*hdr*"
)
and
(
if {?Select One}= "History" then
{table.field} =<some value>
else
if {?Select One}= "Last Full Month" then
{table.field} in lastfullmonth
else
if {?Select One}= "Last Full Week" then
{table.field} in lastfullweek
)
and
(
(if {?Priority} <> "All" then
{table.field} = {?Priority})
else
(if {?Priority} = "All" then
true
)
and
(
if {?Status} <> 2 then
{call_req.active_flag} = {?Status})
else
if {?Status} = 2 then
true
)
and
(
if trim({?Assignee}) <> "" then
{ctct_1.c_last_name} like "+{?Assignee}+"*"
else
if trim({?Assignee}) = "" then
true
)

You might want to look at my FAQ on creating record selection formulas:

faq767-3825

-k

 

[rorymo]

synapsevampire,
Thanks very much for the info. I'm sorry I didn't post the formulas.
I was using them because the customer did not want to use the calendar to pick the dates so I defined date ranges using formulas.
Anyway, I appreciate your help with the code, I will definitely make use of it in the report. Also, thanks for the link to the FAQ, it will really help.
Rory