Ask Your Question

How do I get base to display 0 value instead of NULL? [closed]

asked 2013-09-13 17:22:30 +0100

Libre Lyrae gravatar image

updated 2015-08-29 13:47:35 +0100

Alex Kemp gravatar image

I have tried many different ways using SQL in the queries and using formulas in the report builder and have had no success getting it to insert 0 in place of NULL values.

For example I tried: IF(ISBLANK([OrderAdjustments]);0;[OrderAdjustments]) in the reports

In the Base Guide in chapter 6 it says to use IF([numericfield];[numericfield];0) which I tried also without success.

I tried a CASE WHEN ELSE END statement in my SQL queries without success.

How do I get the database to replace Null values with zero?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-07 01:42:13.479893


Interesting update to this issue - not only can I not get report builder to display 0 instead of NULL, it is also replacing zero with NULL!?!?! What is going on here? I run the query and it shows that all of my zeros are in place, which have been entered into to database via default to zero (ie tax fields default to zero when there is no tax so I don't get NULLs in my calculations). But when I load that same query into a report it is replacing those default zeros with NULL! Help!

Libre Lyrae gravatar imageLibre Lyrae ( 2013-09-20 18:45:26 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2014-12-14 18:21:08 +0100

Alex Thurgood gravatar image

updated 2014-12-14 18:21:47 +0100

My first thought would to be to change your SQL query, rather than fiddling with the report : IF(FieldName IS NULL,'NULL',FieldName)

Note that this was reported as a bug in previous versions, and may have since been fixed.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-09-13 17:22:30 +0100

Seen: 1,543 times

Last updated: Dec 14 '14