Dealing with null value

Printed From: Crystal Reports Book — Forum Name: Technical Questions

Posted By: Beaurou — 11 Oct 2018 at 4:08am

I have 2 tables, one list all the projects and the other provide a list of task for the project. In the task table we have a field call LANG_CODE. Basically the LANG_CODE is EN for English and CF for French and null value if any is selected.

In the Select expert for the project I created a parameter field to enter the project number

{?Project}

If I want to query for all EN in the project I used this formula that work well

{LDMXA.EIACODXA} = {?Project} and
{LDMCC.LANG_CODE} = "EN"

When I try to modify the formula to include the null value with this formula it run the query but on all project not the one I specify in my parameter field. This is my formula

{LDMXA.EIACODXA} = {?Project} and
isnull({LDMCC.LANG_CODE}) or {LDMCC.LANG_CODE} = "EN"

I search the web for a solution with no result

Posted By: kevlray — 11 Oct 2018 at 4:16am

First the isnull check should be first snull({LDMCC.LANG_CODE}) or {LDMCC.LANG_CODE} = "EN"and {LDMXA.EIACODXA} = {?Project}

Quirk of Crystal Reports.

Second, make sure 'Default Values for Nulls' is selected in the Formula Workshop.

Also mixing OR's and And's without using parenthesis could give some odd results.

Posted By: Beaurou — 11 Oct 2018 at 6:58am

Thank for the answer. I had to add the () as follow

(isnull({LDMCC.LANG_CODE}) or {LDMCC.LANG_CODE} = "EN") and
{LDMXA.EIACODXA} = {?Project}

Everything work perfectly