Hi all, I need some help with record selection. I need Crystal to select all records if this one parameter is blank, otherwise select where the record is equal to the parameter. It sounds simple but I've tried suggestions I've searched for but they didn't work.

What keeps happening is the SQL string gets appended with this if I send in a blank:

"Table"."Column"=''

It works fine if I send in a parameter.

Right now I have this:

(if (NOT isNull({?Parm})) then {Table.Column} = {?Parm})

Which gives me the SQL above.

I'm using version 14.

That didn't work. I still have the "Table"."Column"='' showing up in my SQL Query and no data.

This is the record selection:

(if {?Parm1?} = 'Y' then {SomeTable&Col} ='Y' else 1=1) and

(if {?Parm2}='N' then {SomeTable&Col}='P' else 1=1) and

{SomeTable&Col} = {?Parm3} and

{SomeTable&Col}>={?Parm4} and

{SomeTable&Col}<={?Parm5} and

{SomeTable&Col} in {?Parm6} to {?Parm7} and

(NOT(hasvalue({?Parm})) or {?Parm}={SomeTable&Col})

This is the SQL it generates:

SELECT Stuff

 FROM  Location

 WHERE  "SomeTable&Col"='P' AND "SomeTable&Col"=44 AND ("SomeTable&Col">={ts '1950-01-01 00:00:00'} AND "SomeTable&Col"<{ts '2050-12-02 00:00:00'}) AND "SomeTable&Col"=6093 AND "SomeTable&Col"=''

 ORDER BY "SomeTable&Col", "SomeTable&Col", "SomeTable&Col", "SomeTable&Col"

Where Parm = blank


Edited by DrPepperholik - 04 Apr 2014 at 5:03am

I see you revised your post.

Edited by DBlank - 04 Apr 2014 at 5:23am

I am not using version 14 but I believe it has the option for each of the parameter set ups to allow you to set to null or not ... equates to the hasvalue() in the formula). you can try this...