Crystal Reports Set a field to NULL

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions

Topic: Set a field to NULL

Author	Message
lolly54 Groupie Joined: 25 Sep 2011 Online Status: Offline Posts: 58	Topic: Set a field to NULL Posted: 10 Oct 2013 at 1:08am
	I have a tricky question... If the field {Product.Number} is 0, can I set it to NULL in crystal? Anyone? Thanks!
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 10 Oct 2013 at 3:34am
	Not really. There is a trick to do this with strings but I dont think it works for numbers. However if it is for display you can conditionally suppress it, if it is for calculations you can use shared variable formula or Running Totals to exclude them from your calculations.
	IP Logged

lolly54 Groupie Joined: 25 Sep 2011 Online Status: Offline Posts: 58	Posted: 10 Oct 2013 at 9:53pm
	I tried suppressing it if "currentfieldvalue = 0", however, the number is still showing. In the end, I set the font colour condition to display white if it is 0. This is not the perfect way to do, at least it is "working" now. Thanks! :)
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Oct 2013 at 4:11am
	Is it possible it is not actually zero but displaying as zero because it is rounding down to display the integer. Others have had this issue and resoved it using a <1 condition. You could use a round(x,0) or for suppression round(currentfieldvalue,0)=0 It does not make sense that your conditional color formula works but perhaps it is written just differently enough to account for something. Also be aware that if you are summing the field it will sume the actual field value not the displayed (or hidden) 0 so it can alter your expected results.
	IP Logged

Traceyc Newbie Joined: 16 May 2013 Online Status: Offline Posts: 5	Posted: 15 Oct 2013 at 2:58pm
	You can set a 0 number to null by creating a formula with nothing in it and saving it. Reference it as: if{Product.Number} = 0 then {@Null} else {Product.Number} (or something similar). This works perfectly when dealing with strings. If you get an error saying something like it wants a number or wants a string just use tonumber({@Null}). Also works with dates, just use date({@Null}).
	IP Logged

Shruthi Newbie Joined: 19 Jul 2016 Location: India Online Status: Offline Posts: 1	Posted: 21 Jul 2016 at 7:44pm
	The other simplest way to set a 0 to NULL is, changing the font of 0 to white. This can be done by 1) Right click on the required field and select 'Format Field' 2) Select 'Font' 3) Then select 'Color' 4) Write the formula as if {@number}=0 THEN crWhite Note : @number is a formula field
	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.