Print Page | Close Window

Is null or not equal to field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17647
Printed Date: 29 Apr 2024 at 4:43am


Topic: Is null or not equal to field
Posted By: db3712
Subject: Is null or not equal to field
Date 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.


Replies:
Posted By: DBlank
Date Posted: 27 Sep 2012 at 11:50am
NOT(isnull(field)) and field <>'SAP'
 
although <>'SAP' should already exclude any NULL values

Posted By: comatt1
Date 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}<>''

Posted By: DBlank
Date 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'

Posted By: db3712
Date 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"

Posted By: DBlank
Date 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.

Posted By: db3712
Date Posted: 28 Sep 2012 at 8:12am
That did it!  Thank you very much for everyone's help.


Print Page | Close Window