 # Thread: Calculated field defaults to zero

1. ## Calculated field defaults to zero

I have a calculated field that does a simple field1 - field2 numerical calc. I've noticed that the calculated field defaults to zero when adding a new record. This is despite the initial value being set to Null in A5 and to nothing in my Access Db. I've also tried setting the default value in Access to Null but to no avail.

If I remove the calculation from the field the default value is null, so it certainly appears that by having a calculation defined I get a zero.

Does anyone know how to avoid showing zero in a calculated field.

Regards, Richard.  Reply With Quote

2. ## Re: Calculated field defaults to zero

Change your calculated field to use a custom function like result_of(field1,field2) then in the javascript functions define a function
like
Code:
```function result_of(field1,field2){
if(field1-field2 != 0 && field1-field2 != null){
var c = field1-field2;
return c;
}else{
return '';
}
}```
Or, if you choose, you can do it all in the calculated field:
Code:
`if(field1-field2 = 0,'',field1-field2)`  Reply With Quote

3. ## Re: Calculated field defaults to zero

Thanks Jim, that's a good idea... although in my case 0 may be a valid result from the calculation so I would want to show the result as 0 if field 1 and field 2 aren't null and the result is 0. It's a simple mod of your example to do this so thanks.

Another idea I came up with is to use a show/hide expression - to hide the calculated field and then display it when field1 and field2 aren't empty.

Regards, Richard.  Reply With Quote

4. ## Re: Calculated field defaults to zero

Another idea is to put this :

alltrim(str( convert_type(<value>,"N") ,250,2,"B")) - 2 decimals

alltrim(str( convert_type(<value>,"N") ,250,0,"B")) - NO decimals

in DISPLAY FORMAT in the DISPLAY SETTINGS on the CALCULATED FIELD.

Basically, it means displaying any number with 2 decimals and NOTHING if the number is 0.

Mike.Blank if zero.jpg  Reply With Quote

5. ## Re: Calculated field defaults to zero

This is ugly, but I think it does what you want.
I assume a dialog with 3 numeric fields, and a button to initiate the check.

Code:
```if({dialog.Object}.getValue('FIELD1')=='' ||{dialog.Object}.getValue('FIELD2')==''){

{dialog.Object}.setValue('FLDRESULT','' );
alert('you have a null value, no math will be performed');
}
else{{dialog.Object}.setValue('FLDRESULT',{dialog.Object}.getValue('FIELD1')-{dialog.Object}.getValue('FIELD2'))}```  Reply With Quote

6. ## Re: Calculated field defaults to zero

If you want to make sure only numbers are processed, this code is better:

Code:
```if(isNaN({dialog.Object}.getValue('FIELD1'))||{dialog.Object}.getValue('FIELD1')==''||isNaN({dialog.Object}.getValue('FIELD2'))||{dialog.Object}.getValue('FIELD2')=='')
{{dialog.Object}.setValue('FLDRESULT','' );  Reply With Quote