Null values

[Penny]

If have coded a report field as follows:

=Sum(Val(nulltozero([FieldName)))


Function nulltozero(AnyValue As Variant) As Variant
' Coverts null values to zero
If IsNull(AnyValue) Then
nulltozero = 0
Else
nulltozero = AnyValue
End If

End Function

I'm still getting blank data in some of my fields along
with 0.00.

Any suggestions on how to fix?

Thanks in advance.

Penny

 

[John Spencer (MVP)]

Could the fields have a zero-length string in them? You might try changing your
if statement to

If Len(Trim(AnyValue & vbnullstring)) = 0 Then
NullToZero = 0
Else
...

That is a really paranoid way of handling nulls, multiple spaces, and
zero-length strings as equivalents.

 

[Bill Crawford]

Why complicate life?

place:

IIF([FieldName] is null, 0, [FieldName])
in the field control source of the report