0

Convert a null value to a default value ISNULL equivalent

Jet Reports Historic Posts

May 09, 2013 15:03

None

Follow

Hi
I am trying to sum a field in the database where the values are not always set and therefore appear as null in the database giving an error. Is there a way when doing a NL("SUM"…. to 'default' to a null value (i.e. 0). Equaivalent to ISNULL in SQL?

Thanks

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts May 09, 2013 17:26

  You could do this with a SQL= statement something like this:
  
  =NL(,"SQL=SELECT SUM(ISNULL(Field,0)) as Sum FROM Table","Sum")
  There's more information on SQL= in the Jet help here: http://help.jetreports.com/12.5/Essentials/SQL=.html Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts May 09, 2013 19:22

  Thanks. Unfortunatley I am running Jet with NAV and I am lead to believe that SQL statements are not possible.

• 0
  
  Jet Reports Historic Posts May 09, 2013 20:20

  You are using NAV? How did you ever get null values into a numeric field in NAV? I don't believe this is possible unless you went in and added the fields and values directly through SQL Server instead of through the NAV interface. In the NAV interface, typically numeric fields get a zero, not a null. Also, typically when you create a field in a table in the NAV user interface, the SQL field that is created to store the values is marked as "not null" by Nav. How did you get nullable fields with null values into your NAV database? Also, you are using a Nav data source type in Jet, not a Universal data source right?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts May 10, 2013 11:01

  Hi
  
  Apologies. I am more used to SQL than NAV and the field is demonstrating the same characteristics I would see through SQL. It is indeed a zero value. I am using the field in a division statement which should give a divide by zero error but just gives a Fault. Knowing there were potential unentered values I just assumed they were null. Moral: Never assume anything.

• 0
  
  Jet Reports Historic Posts May 10, 2013 12:36

  Aaah I see. If you are getting a #VALUE from a Jet formula, you should be able to use Jet -> Debug to find out what the problem is. If it's a different type of Excel error then it's probably not coming from Jet. I hope that helps.
  
  Regards,
  Hughes