Hi,

I'm having a little trouble with a boolean parameter and a selection criteria IF statement.

I'm running CR 2008.  I have a boolean as a parameter ({?Determination Code}) and code in the selection criteria as follows:

{audmmast.dda_cd2} = {?Category - min_ctg} and
{audmmast.dda_cd1} = {?Category - maj_ctg} and
{audmmast.doc_typ} = 4 and
(if ({?Determination Code} = False) then
    isnull({audmmast.det_cde}))

If I select False at the prompt, then only those records with a null in the det_cde field are displayed (as long as they suit the other criteria as well) which is what I want.  However if I select True then no records are displayed.

I figured I would need an 'else' clause but am unsure what to put so that it selects everything no matter what the det_cde field has in it (null or value).

Or have I got the selection criteria code wrong in the first place?

Thanks in advance

justin

UPDATE:

Interesting; I changed the selection formula to be:

{audmmast.dda_cd2} = {?Category - min_ctg} and
{audmmast.dda_cd1} = {?Category - maj_ctg} and
{audmmast.doc_typ} = 4 and
(if ({?Determination Code} = False) then
    isnull({audmmast.det_cde})
else if ({?Determination Code} = True) then
    ({audmmast.det_cde} > "" or isnull({audmmast.det_cde})))

and it only returns those where det_cde is not null if I have chosen True.  After running the report I copied and pasted the resulting SQL from CR into Microsoft Query, and Microsoft Query shows all the records the way I want.


Edited by justin - 15 Oct 2009 at 10:30pm