Performing calculations with empty records

[Guest]

Hello.

I'm trying to arranje a function to perform calculations with fields ("Lot"
and "Dem") that have some empty records. For example, if "lot" is not empty
and "Dem" is empty, the result of the function should the one defined in the
function below (which doesn't work): 100.

The thing is that I can't define/recall what is inside the empty
cell/record. Is is the type of the variables which is not correct?

Can anyone help me? Thanks.

Function ILF(ParamArray Fields() As Variant)

Dim Lot As Variant
Dim Dem As Variant

If Lot = Empty Then
ILF = Empty
Else
If Dem = Empty Then
ILF = 100
Else
ILF = (100 + Lot * 10 - Dem) / (100 + Lot * 10) * 100
End If
End if

End Function

 

[Rick B]

I've never seen "=empty" before.

You can check to see if it is null by using

IsNull([ILF]

Or you can use the Nz function ot assign a value to a null...

Nz([ILF],100)



Hope that helps,

Rick B

 

[Tim Ferguson]

I'm trying to arranje a function to perform calculations with fields
("Lot" and "Dem") that have some empty records.

Like Rick, I am a bit perplexed by this. Records have fields which may be
empty (i.e. NULL), not the other way round!

comments in-vba-line below

' Function ILF(ParamArray Fields() As Variant)
' cannot see where the array comes from: why not just
' pass the values?
' And you haven't specified the return data type
'
Private Function ILF(Lot as Variant, Dem as Variant) As Variant
' all args and returns are Variants because they have to be
' null-able

If IsNull(Lot) Then
ILF = Null

ElseIf IsNull(Dem) Then
ILF = 100

Else
' okay, they both have real numeric(?) values
ILF = (100 + Lot * 10 - Dem) / (100 + Lot * 10) * 100

End If

' all done
End Function

As far as I can tell, I think this does what you want; but I don't really
know what that is.

Hope it helps


Tim F

 

[Andi Mayer]

=?Utf-8?B?RnJhbmNpc2NvIEYu?= <[email protected]>
wrote in Like Rick, I am a bit perplexed by this. Records have fields which may be
empty (i.e. NULL), not the other way round!

empty is available, but "useless" exept for variants

it works: (immidiate window)
?aaa=empty
True

from the help

IsEmpty returns True if the variable is uninitialized, or is
explicitly set to Empty; otherwise, it returns False. False is always
returned if expression contains more than one variable. IsEmpty only
returns meaningful information for variants.