NVL equivalent?

[sammybee77]

Hi guys, I was wondering if there was an equivalent to the SQL NVL function in Crystal, the problem I have is that my formula is a sum of field 1 add field 2 add field 3. The problem is that if any of the results are null then the result of my formula is null. Any ideas?

Many thanks

Sam

 

[Ngolem]

Are you wanting this as an SQL expression? I only have CR7 here so I am not sure if an "isnull" function exists there but certainly you can handle this condition in a formula

the formula would look like the following: I'll assume that if a field is null then you would consider it to be a zero

Whileprintingrecords;
numberVar result;
numberVar field1 := 0;
numberVar field2 := 0;
numberVar field3 := 0;

if not isnull({table.field1value}) then
field1 := {table.field1value};

if not isnull({table.field2value}) then
field2 := {table.field2value};

if not isnull({table.field3value}) then
field3 := {table.field3value};

result := field1 + field2 + field3;

result;

that should do it.

You could also make the report such that NULLS are converted to a default value (ie. zero) but I don't like doing that because unless you consistantly do this it can be a debugging nightmare if you don't remember doing that later...this way it is clear what you have done

Jim

JimBroadbent@Hotmail.com