Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Is null or not equal to field Post Reply Post New Topic
Author Message
db3712
Groupie
Groupie
Avatar

Joined: 30 Oct 2008
Location: United States
Online Status: Offline
Posts: 44
Quote db3712 Replybullet Topic: Is null or not equal to field
    Posted: 27 Sep 2012 at 11:34am

I have an issue where my report isn't pulling data if a field is not null.  I need my formula to pull data if the field is not equal to "SAP" or is not null.  Any ideas?  Thanks ahead of time.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Sep 2012 at 11:50am
NOT(isnull(field)) and field <>'SAP'
 
although <>'SAP' should already exclude any NULL values


Edited by DBlank - 27 Sep 2012 at 11:51am
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet Posted: 28 Sep 2012 at 5:12am
you may want to also check if a value is ""

so either test len({field})>=1 or {field}<>''
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Sep 2012 at 5:29am
After reading the post title I think you meant to ask how to get <>'SAP' or null.
Either change the option in the formula editor to 'use default values for null'

Or use
Isnull(field) or field<>'SAP'
IP IP Logged
db3712
Groupie
Groupie
Avatar

Joined: 30 Oct 2008
Location: United States
Online Status: Offline
Posts: 44
Quote db3712 Replybullet Posted: 28 Sep 2012 at 6:45am
Ok guys.  I'm losing my mind.  I appreciate your previous replies.  I'm able to get the data now based on your ideas, but depending upon where I place IsNull({service_call.call_source_id}) or {service_call.call_source_id} <> "SAP", other portions of my formula is ignored.  Here's my complete formula.  Based on this formula, it will pull employees even if my formula says otherwise.  If I move the isnull lines around, different results occur.
 
{order_line.order_type_id} = "service_order" and
IsNull({service_call_env.project_code}) or {service_call_env.project_code} <> "BICYCLE_TAIWAN" and
{person.is_employee} = "N" and
{order_stat.descr} <> "AdvancedBilled" and
not ({service_call.callt_id} in ["NO_CHARGE_ACCESSORIES", "NO_INVOICE_RECEIVED", "TRACKING"]) and
not ({service_call_env.bunn_service_type} in ["CUSTOMER_CANCELLED_ORDER", "PHONE_FIXED"]) and
{order_line.order_stat_uniq_id} in [500, 600, 700] and
{service_call_env.resolve_date} in currentdate to Currentdate - 180 and
not ({service_call.sa_person_id} in ["13703-1", "CANADA"]) and
IsNull({service_call.call_source_id}) or {service_call.call_source_id} <> "SAP"


Edited by db3712 - 28 Sep 2012 at 6:53am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Sep 2012 at 7:27am
Nulls are handled in an unusual way in crystal.
You must always put the isnull statement as the first instance of referencing that field.
If it is not put first and the formula hits a null it will stop evaluating the formula.
It is often easier to use the option in the formula editor to force it to evaluate nulls as the default value.
You shouldbe able to fix your statement by putting parenthesis around your two or statements with the isnull parts.
IP IP Logged
db3712
Groupie
Groupie
Avatar

Joined: 30 Oct 2008
Location: United States
Online Status: Offline
Posts: 44
Quote db3712 Replybullet Posted: 28 Sep 2012 at 8:12am
That did it!  Thank you very much for everyone's help.
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.