Print Page | Close Window

Dealing with null value

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22679
Printed Date: 03 May 2024 at 4:53pm


Topic: Dealing with null value
Posted By: Beaurou
Subject: Dealing with null value
Date Posted: 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

-------------
Beaurou


Replies:
Posted By: kevlray
Date Posted: 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
Date Posted: 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

-------------
Beaurou


Print Page | Close Window