Ignore Null values in formula

[wld1957]

I have used the "ToNumber and Towords" in forumulas to change text fields. I have run into a problem when there is no value in the field. It stops the formula and brings up the text box that says "the string is non numeric".

I group records by a sale date but the fields that I use the forumula does not always have a figure or date in them and the fomula's always stop when it gets to a record that has a null value.

Is there a way to ignore null values even if the formula has it changing from a text field to a different field?

This is the formula: TONUMBER ({cpmain.udftext6})
I then use another formula with "towords" to show the numerical value that has been entered as both numbers and written out as in One Hundred dollars.

I'm using CRXI for this report. Since I do not have field for money I wanted to be able to put in the amount into this field and then have it convert it to a number. It works great, but only for single records. Any help would be greatly appreciated. Thanks in advance.

 

[Patrick69]

Please keep in mind I am using a version of CR from another time (7..gulp) but I believe you can just put:

if IsNull({cpmain.udftext6}) = false then TONUMBER ({cpmain.udftext6})
else 0;

 

[wld1957]

The formula you gave reads like it should work but I still get the "The string is non numeric" window when it hits the blank field. It then highlights the following text in the formula. "TONUMBER ({cpmain.udftext6})".

 

[Patrick69]

try :

If Not isNull({cpmain.udftext6}) Then
If isNumeric({cpmain.udftext6}) Then ToNumber({cpmain.udftext6})
Else 0

I believe you can also change the way CR handles NUL fields also but it is probably very different on my version than yours.

 

[synapsevampire]

Try:

If Not(isNull({cpmain.udftext6}))
and
isNumeric({cpmain.udftext6}) Then
towords(val({cpmain.udftext6}))
Else
"no value"

-k

 

[wld1957]

That worked, thanks for all the help. I still have much to learn....