How to encapsulate IIF and ISNULL call in Access 2010 VBA?

Earth Engine picture Earth Engine · Jul 13, 2012 · Viewed 9.1k times · Source

I use the following expression pattern in my Access 2010 database frequently:

IIF(ISNULL(fieldName), Null, myFunction(fieldName))

Here myFunction is a user defined function that turns the value of fieldName into another format, if it does exists.

Just for a little bit reduce of typing, I tried to define the following function:

Function IifIsNull(p AS Variant, v AS Variant) AS Variant
    If IsNull(p) Then IifIsNull = p Else IifIsNull = v
End Function

and I supposed to use it in the following way:

IifIsNull(fieldName, myFunction(fieldName))

But it does not work. When fieldName is Null, the return value of the IifIsNull is #Error, and IifIsNull has even not been called!

Is it possible to simplify the given code pattern with a user defined function or system function at all?

UPDATE:

There are a couple of myFunctions, and all of those functions are currently strongly typed, a simple example is the following:

Function RemoveSpace(str AS String) AS String
    For i=1 to Len(str)
        If Mid(str,i,1) <> " " Then RemoveSpace = RemoveSpace + Mid(str,i,1)
    Next
End Function

Answer

Jean-Fran&#231;ois Corbett picture Jean-François Corbett · Jul 13, 2012

I assume that myFunction(fieldName) throws an error when fieldName is null.

When you call IifIsNull(fieldName, myFunction(fieldName)), the first thing that gets evaluated is myFunction(fieldName). So if fieldName is null, you get an error.