Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Force value to null Post Reply Post New Topic
Author Message
martinjamesward
Newbie
Newbie


Joined: 22 Jun 2009
Location: United Kingdom
Online Status: Offline
Posts: 11
Quote martinjamesward Replybullet Topic: Force value to null
     Posted: 02 Jul 2010 at 2:36am
I have field which can either contain a numeric value or an error message (due to the way my database table has been designed).
 
I need to average the numeric value and want to force any records with an error to null, not 0 as using 0 will effect my averages.
 
I can not exclude records exhibiting this error as they need to show on the report.
 
What I really want is an if formula that will allow me to force a null value where the data in my field is not numeric.
 
Is there any method of doing this?
 
Thanks to anyone who can help in advanced.
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 02 Jul 2010 at 2:48am
Hi
Create a Forumla in Crystal and Check for the field using the following code
 
if not(ISNUMERIC({table.fieldname}))  then
NULL
else
{table.fieldname}
 
hope that works, I am not able to test this at the moment, but if you could post some sample data I can test in evening when i get back home
 
Cheers
Rahul
IP IP Logged
martinjamesward
Newbie
Newbie


Joined: 22 Jun 2009
Location: United Kingdom
Online Status: Offline
Posts: 11
Quote martinjamesward Replybullet Posted: 02 Jul 2010 at 5:15am
Rahul
 
Thats the first thing I tried, it does not like the function Null only IsNull.
 
I have even tried converting the the field to text, using "" as a null text value and then converting back to a number as below...
 
 


Local StringVar tmp;


if IsNumeric({Repair_Tracking_Report_View.Tech_Hours}) then 
    tmp := Cstr(CDbl({Repair_Tracking_Report_View.Tech_Hours})) 
else 
    tmp :=Cstr("");


Cdbl(tmp)




 


but this also fails.

    

   		
  

    

   IP IP Logged
  

  

         

   
    
    rahulwalawalkar
          
Senior Member
      
Senior Member
        
Avatar          

Joined: 08 Jun 2007
              
Location: United Kingdom
        
Online Status: Offline
    
Posts: 731
    

   		
   Quote rahulwalawalkar Replybullet
          Posted: 02 Jul 2010 at 5:26am
  

  

   
    

     Hi

 


Please can you post me some sample data, I will have a look I will also need the following info


 


Database Used:-


Data Type for the field and length


 


I have sql server at home so i can replicate the scenario and see what the issue is


 


Cheers


Rahul

    

   		
  

    

   IP IP Logged
  

  

    

  
  
 







 

  
   

    

     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

 






                    		
                

            

        		
    

    

        
            HOME | FREE EBOOK |
            PROGRAMMING GUIDE |
            PURCHASE |
            REVIEWS |
            CONTACT ME |
            FAQ |
            LINKS |
            PRIVACY |
            CLICK PLAYS |
            BAEZL 


            
© 2004-2026 Brian Bischof. All Rights Reserved.