Crystal Reports Is null or not equal to field

Report Design
Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Is null or not equal to field

Author	Message
db3712 Groupie Joined: 30 Oct 2008 Location: United States Online Status: Offline Posts: 44	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 Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Logged

comatt1 Senior Member Joined: 19 May 2011 Online Status: Offline Posts: 337	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 Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Logged

db3712 Groupie Joined: 30 Oct 2008 Location: United States Online Status: Offline Posts: 44	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 Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Logged

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