Handling Nulls in User-Defined Functions 
Author Message
 Handling Nulls in User-Defined Functions

I am having an extraordinarily difficult time figuring out how to handle
fields that have no entry or are null in VBA when creating user-defined
functions. I have a table in which a number of fields are optional. I want a
user-defined function to return a different value depending on whether the
field is null or not. If I use the expression builder in a query or unbound
form field, the following expression works just fine whether or not the
field is null:

    IIF(IsNull(strName),"NS",strName)

However, when I attempt to use it in a VBA module, it works fine if the
field is not null, but it returns a #Error if it is. I've researched a half
a dozen third party manuals and tried all the following code samples:

Public Function OrderingInfo(strName As String) As String

    Dim strTemp As String

    strTemp = IIf(IsNull(strName), "NS", strName)

    OrderingInfo = strTemp

End Function
Public Function OrderingInfo2(strName As String) As String

    Dim strTemp As String

    strTemp = IIf(IsNull(CVar(strName)), "NS", strName)

    OrderingInfo2 = strTemp

End Function
Public Function OrderingInfo3(strName As String) As String

    Dim strTemp As String

    strTemp = IIf(Nz(strName) = "", "NS", strName)

    OrderingInfo3 = strTemp

End Function
Public Function OrderingInfo4(strName As String) As String

    Dim strTemp As String

    strTemp = IIf(Nz(strName, 0) = 0, "NS", strName)

    OrderingInfo4 = strTemp

End Function
Public Function OrderingInfo5(strName As String) As String

    Dim strTemp As String

    strTemp = IIf(strName = "", "NS", strName)

    OrderingInfo5 = strTemp

End Function

All of them, and a dozen or so other variations, return #Error if the
refernce field is null and the correct response if it isn't. I of course am
not at all interested in getting "NS" back if the field is null; I want to
do some much more complex things that cannot be done in a expression.
However, I can get there, if I can't figure out how to get VBA to accurately
return True or False to something equivalent to the statement:

    If IsNull(strName) Then
        ...

This seems like such a simple thing, and I'm sure that I'm overlooking the
obvious, but I can't find a single code sample that deals with it. (Or more
correctly, I have foudn some that purport to do so, but they all return
#Error, too.) I hope someone can help with this conundrum!

Larry



Fri, 11 Nov 2005 04:55:01 GMT  
 Handling Nulls in User-Defined Functions
VBA handles IIf differently than when you use it in a query window or other
places that SQL handles it. In VBA, both answers to the IIf are evaluated,
regardless of the value of the condition. Only the correct answer is
returned, but both are evaluated. You could eliminate this by using

Select Case
If, Then, Else
Nz

or by enclosing the answer part that causes the problem (the false part in
this case) in Nz.

strTemp = IIf(IsNull(strName), "NS", Nz(strName, ""))

In fact, in the example you have here, you could use Nz instead of IIf

strTemp = Nz(strName, "NS")

This will return strName if it is not Null and will return NS if it is Null.

--
Wayne Morgan
Microsoft Access MVP


Quote:
> I am having an extraordinarily difficult time figuring out how to handle
> fields that have no entry or are null in VBA when creating user-defined
> functions. I have a table in which a number of fields are optional. I want
a
> user-defined function to return a different value depending on whether the
> field is null or not. If I use the expression builder in a query or
unbound
> form field, the following expression works just fine whether or not the
> field is null:

>     IIF(IsNull(strName),"NS",strName)

> However, when I attempt to use it in a VBA module, it works fine if the
> field is not null, but it returns a #Error if it is. I've researched a
half
> a dozen third party manuals and tried all the following code samples:

> Public Function OrderingInfo(strName As String) As String

>     Dim strTemp As String

>     strTemp = IIf(IsNull(strName), "NS", strName)

>     OrderingInfo = strTemp

> End Function
> Public Function OrderingInfo2(strName As String) As String

>     Dim strTemp As String

>     strTemp = IIf(IsNull(CVar(strName)), "NS", strName)

>     OrderingInfo2 = strTemp

> End Function
> Public Function OrderingInfo3(strName As String) As String

>     Dim strTemp As String

>     strTemp = IIf(Nz(strName) = "", "NS", strName)

>     OrderingInfo3 = strTemp

> End Function
> Public Function OrderingInfo4(strName As String) As String

>     Dim strTemp As String

>     strTemp = IIf(Nz(strName, 0) = 0, "NS", strName)

>     OrderingInfo4 = strTemp

> End Function
> Public Function OrderingInfo5(strName As String) As String

>     Dim strTemp As String

>     strTemp = IIf(strName = "", "NS", strName)

>     OrderingInfo5 = strTemp

> End Function

> All of them, and a dozen or so other variations, return #Error if the
> refernce field is null and the correct response if it isn't. I of course
am
> not at all interested in getting "NS" back if the field is null; I want to
> do some much more complex things that cannot be done in a expression.
> However, I can get there, if I can't figure out how to get VBA to
accurately
> return True or False to something equivalent to the statement:

>     If IsNull(strName) Then
>         ...

> This seems like such a simple thing, and I'm sure that I'm overlooking the
> obvious, but I can't find a single code sample that deals with it. (Or
more
> correctly, I have foudn some that purport to do so, but they all return
> #Error, too.) I hope someone can help with this conundrum!

> Larry



Fri, 11 Nov 2005 05:25:33 GMT  
 Handling Nulls in User-Defined Functions

Quote:

>However, when I attempt to use it in a VBA module, it works fine if the
>field is not null, but it returns a #Error if it is. I've researched a half
>a dozen third party manuals and tried all the following code samples:

>Public Function OrderingInfo(strName As String) As String

If you define strName as Variant your problems will cease. A String
datatype cannot accept NULL as a value; a Variant can.

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Fri, 11 Nov 2005 05:35:39 GMT  
 Handling Nulls in User-Defined Functions
An additional example, this will fail also in VB or VBA:

y = IIf(x = 0, 3, 3 / x)

If x = 0 you will get a divide by zero error because the 3 / x will be
evaluated, even though that answer isn't used. In this case the easiest way
out may be:

If x = 0 Then
    y = 3
Else
    y = 3/ x
End If

--
Wayne Morgan
Microsoft Access MVP



Fri, 11 Nov 2005 05:38:57 GMT  
 Handling Nulls in User-Defined Functions
A thousand thanks! I could swear that I had tried that variation, but in my
frustration I probably compounded the problem by doing something else wrong.
I knew that only variants can accept NULL as a value, and I had tried
several approaches to dealing with that. I was also a little worried that
the data was coming from a table text field and wasn't sure how VBA would
handle casting the data type when passed as a function parameter. But it
works! For the benefit of anyone else who might be interested in this VBA
issue, here's my test procedure:

Public Function OrderInfo9(varName as Variant) As String

    Dim strTemp As String

    If IsNull(varName) Then
        strTemp = "Not specified"
    Else
        strTemp = CStr(varName)
    End If

    OrderInfo9 = strTemp

End Function

As you can probably imagine, I plan on doing a lot more within the If...Then
block, but this gets me on my way. Thanks again.

Larry


Quote:

> >However, when I attempt to use it in a VBA module, it works fine if the
> >field is not null, but it returns a #Error if it is. I've researched a
half
> >a dozen third party manuals and tried all the following code samples:

> >Public Function OrderingInfo(strName As String) As String

> If you define strName as Variant your problems will cease. A String
> datatype cannot accept NULL as a value; a Variant can.

>                   John W. Vinson[MVP]
>     Come for live chats every Tuesday and Thursday
> http://go.compuserve.com/msdevapps?loc=us&access=public



Fri, 11 Nov 2005 06:13:30 GMT  
 Handling Nulls in User-Defined Functions
Thanks for your quick response. However, in the way that I implemented your
suggestions, they produced the same #Error message. Specifically, all of
these produce #Error:

Public Function OrderInfo6(strName As String) As String

    Dim strTemp As String

    If IsNull(strName) Then
        strTemp = "Not specified"
    Else
        strTemp = strName
    End If

    OrderInfo6 = strTemp

End Function

Public Function OrderInfo7(strName As String) As String

    Dim strTemp As String

    strTemp = IIf(IsNull(strName), "NS", Nz(strName, ""))

    OrderInfo7 = strTemp

End Function

Public Function OrderInfo8(strName As String) As String

    Dim strTemp As String

    strTemp = Nz(strName, "NS")

    OrderInfo8 = strTemp

End Function

The answer lay in what John Vinson speaks to in his reply to my original
article. This code works perfectly:

Public Function OrderInfo9(varName As Variant) As String

    Dim strTemp As String

    If IsNull(varName) Then
        strTemp = "Not specified"
    Else
        strTemp = CStr(varName)
    End If

    OrderInfo9 = strTemp

End Function

It's probably that I misunderstood what you were suggesting, but I've
included this experience for the benefit of anyone else who comes across
this posting.

Again, thanks for your response.

Larry


Quote:
> VBA handles IIf differently than when you use it in a query window or
other
> places that SQL handles it. In VBA, both answers to the IIf are evaluated,
> regardless of the value of the condition. Only the correct answer is
> returned, but both are evaluated. You could eliminate this by using

> Select Case
> If, Then, Else
> Nz

> or by enclosing the answer part that causes the problem (the false part in
> this case) in Nz.

> strTemp = IIf(IsNull(strName), "NS", Nz(strName, ""))

> In fact, in the example you have here, you could use Nz instead of IIf

> strTemp = Nz(strName, "NS")

> This will return strName if it is not Null and will return NS if it is
Null.

> --
> Wayne Morgan
> Microsoft Access MVP



> > I am having an extraordinarily difficult time figuring out how to handle
> > fields that have no entry or are null in VBA when creating user-defined
> > functions. I have a table in which a number of fields are optional. I
want
> a
> > user-defined function to return a different value depending on whether
the
> > field is null or not. If I use the expression builder in a query or
> unbound
> > form field, the following expression works just fine whether or not the
> > field is null:

> >     IIF(IsNull(strName),"NS",strName)

> > However, when I attempt to use it in a VBA module, it works fine if the
> > field is not null, but it returns a #Error if it is. I've researched a
> half
> > a dozen third party manuals and tried all the following code samples:

> > Public Function OrderingInfo(strName As String) As String

> >     Dim strTemp As String

> >     strTemp = IIf(IsNull(strName), "NS", strName)

> >     OrderingInfo = strTemp

> > End Function
> > Public Function OrderingInfo2(strName As String) As String

> >     Dim strTemp As String

> >     strTemp = IIf(IsNull(CVar(strName)), "NS", strName)

> >     OrderingInfo2 = strTemp

> > End Function
> > Public Function OrderingInfo3(strName As String) As String

> >     Dim strTemp As String

> >     strTemp = IIf(Nz(strName) = "", "NS", strName)

> >     OrderingInfo3 = strTemp

> > End Function
> > Public Function OrderingInfo4(strName As String) As String

> >     Dim strTemp As String

> >     strTemp = IIf(Nz(strName, 0) = 0, "NS", strName)

> >     OrderingInfo4 = strTemp

> > End Function
> > Public Function OrderingInfo5(strName As String) As String

> >     Dim strTemp As String

> >     strTemp = IIf(strName = "", "NS", strName)

> >     OrderingInfo5 = strTemp

> > End Function

> > All of them, and a dozen or so other variations, return #Error if the
> > refernce field is null and the correct response if it isn't. I of course
> am
> > not at all interested in getting "NS" back if the field is null; I want
to
> > do some much more complex things that cannot be done in a expression.
> > However, I can get there, if I can't figure out how to get VBA to
> accurately
> > return True or False to something equivalent to the statement:

> >     If IsNull(strName) Then
> >         ...

> > This seems like such a simple thing, and I'm sure that I'm overlooking
the
> > obvious, but I can't find a single code sample that deals with it. (Or
> more
> > correctly, I have foudn some that purport to do so, but they all return
> > #Error, too.) I hope someone can help with this conundrum!

> > Larry



Fri, 11 Nov 2005 06:21:06 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Return NULL from a user-defined type function

2. Functions - User Defined Functions

3. User-defined error when the user doesn't define any errors

4. User Control - User-defined type not defined

5. Null error handling in a function

6. how to handle NULL values from a FORM when using date string functions - help please

7. How VB handling user define message?

8. How to map handles/pointers to VB user defined TYPES (help)

9. Handling user-defined messages

10. how to handle NULL values from a FORM when using date string functions - help please

11. Can ScriptCtl Handle User Defined Types?

12. Handling User Defined Events from ActiveX Controls

 

 
Powered by phpBB® Forum Software