Display null as blank

[Vet246]

I'm seriously stuck! I have a report where I'm trying to pull the null values in as 'blank' and the other values in as they are listed in the database. I can't pull the null values in as 0.00 because I have prices in the database that are 0.00. I've tried all of the following formulas and has not had any luck. Either I return no values at all for anything or the nulls populate as 0.00 which makes it so I can't determine what values are 0.00 and what values are actually null. I've tried creating a formula and I've tried conditional suppression formatting with no success. Below are the formulas that I've tried in both places:

if isnull({TMFS.TM_DF_ALW_1}) then "N/A" else totext({TMFS.TM_DF_ALW_1})

if isnull({TMFS.TM_DF_ALW_1}) then "N/A" else (totext({TMFS.TM_DF_ALW_1}))

if {TM_PGP.PG_ALW_1} >= 0 then {TM_PGP.PG_ALW_1} else ".333" (Tried to get a value to populate for null so I could exclude it)

if {TM_PGP.PG_ALW_1} >= 0 then {TM_PGP.PG_ALW_1}

if isnull ({TMFS.TM_DF_ALW_1}) then " " else (totext({TMFS.TM_DF_ALW_1}))

if isnull ({TMFS.TM_DF_ALW_1}) = " " then " " else (totext({TMFS.TM_DF_ALW_1}))

Any help would be greatly appreciated!

 

[pmax9999]

What is the datatype for {TMFS.TM_DF_ALW_1}? How do the "blank" amounts appear on the report?

Under "File ==> Options ==> Reporting" OR "File ==> Report Options" do you have the "Convert Database NULL Values to default". What setting do you have in the formula to deal with NULLS ("Exception for NULLS" or "Default values for NULLS") ?

If the datatype of {TMFS.TM_DF_ALW_1} is number or currency and none of your settings are changing the NULLS to 0, your first formula should work.

Cheers
Pete

 

[Vet246]

I my settings the options are:
1. Convert Database Bull Values to Default
2. Convert Other Null Values to Default

I do not have either of them checked.

This field is a numeric field.

 

[Vet246]

In the formula field I have Exceptions for Nulls

 

[Vet246]

I've also tried the following formulas with no success:

If isnull({TM_PGP.PG_ALW_1}) or trim (totext({TM_PGP.PG_ALW_1})) = "" then "N/A" else (totext({TM_PGP.PG_ALW_1}))

If isnull({TM_PGP.PG_ALW_1}) then .33333 else {TM_PGP.PG_ALW_1}

If not isnull({TM_PGP.PG_ALW_1}) then {TM_PGP.PG_ALW_1} else .333333

IIf(IsNull({TM_PGP.PG_ALW_1}),.3333,{TM_PGP.PG_ALW_1})

IIf(IsNull({TM_PGP.PG_ALW_1}),"N/A",(totext({TM_PGP.PG_ALW_1})))

if isnull({TM_PGP.PG_ALW_1}) or (totext({TM_PGP.PG_ALW_1})) = "" then "NA"
else (totext({TM_PGP.PG_ALW_1}))

 

[pmax9999]

Are you certain there are NULLS? If you put the {TM_PGP.PG_ALW_1} field on the report, how do NULL values display? What result do you get from the very first formula you said you tried?

Pete.

 

[Vet246]

When I display the field with no formula the field displays the null values as 0.00. When I use the first formula that I have listed it still displays the null values as 0.00.

 

[pmax9999]

If the data is showing as 0.00 and is not being changed by Options, Report Options, or other formatting, the data is a 0 and not a NULL. If it was a NULL it would show as being blank.


Pete.

 

[IanWaterman]

If they are zero as suggested by Pete you can format number to be blank if zero

Right click field select format -> Customise -> check suppress if zero

Ian