Function problem with null values

[Derek Wittman]

Good afternoon,
I have a function that I'm calling in a query's calculated field. The
function is:

why(changed, e1, e2).

There are times when e1 or e2 is null. How can I edit my function code to
negate the #Error I get when e1 or e2 is null? It is important that I keep
e1 or e2 null (depending on the record) since that's what this code is all
about.

Here's my small code:

Function why(changed As String, e1 As Double, e2 As Double) As String
If changed = "YES" And (Not e1) And e2 Then why = "Added"
If changed = "YES" And e1 And (Not e2) Then why = "Deleted"
If changed = "YES" And e1 And e2 And (e1 <> e2) Then why = "Modified"
If changed = "NO" Then why = ""
End Function

Any help would be greatly appreciated!
Derek

 

[Derek Wittman]

I really love answering my own question. Seems I needed to call the function
with some Nz() functions for the e1 and e2 values!

And just to be safe, I used the Nz() in the function as well! Worked like a
charm!
Derek

 

[John W. Vinson]

Function why(changed As String, e1 As Double, e2 As Double) As String

If you Dim the arguments as Variant rather than as String or Double, you can
pass in Nulls. You'll still have to use NZ() internally to handle them though.

John W. Vinson [MVP]