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 myFunction
s, 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
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.