Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: Dealing with null value Post Reply Post New Topic
Author Message
Beaurou
Newbie
Newbie
Avatar

Joined: 21 Nov 2015
Location: Canada
Online Status: Offline
Posts: 2
Quote Beaurou Replybullet Topic: Dealing with null value
    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
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Online
Posts: 1440
Quote kevlray Replybullet 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.
IP IP Logged
Beaurou
Newbie
Newbie
Avatar

Joined: 21 Nov 2015
Location: Canada
Online Status: Offline
Posts: 2
Quote Beaurou Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.