ACC2000: "Invalid Use of Null" or "#Error" Error Message in Custom Function (210471)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210471
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

A user-defined function may return one of the following error messages:
Run-time error '94':

Invalid use of Null

-or-

#Error

CAUSE

Null assumes a data type of Variant. You are trying to evaluate or assign Null to a non-Variant data type.

RESOLUTION

Make sure that variables contain valid data or define the variables as Variant. For example: 
Dim MyVar As Variant

instead of
Dim MyVar As String

MORE INFORMATION

Null is an indication that a variable contains no valid data. The following function reproduces this behavior. 
Function Test()
   Dim MyVar As Variant
   Dim Count As Integer, I As Integer

   MyVar = Null
   Count = Null

   For Count = 1 To MyVar
      I = I + Count
   Next Count
End Function
Run the function by pressing the F5 key. This will raise the error described in the "Symptoms" section of this article. The initial error occurs because the function is trying to assign a Null to the variable Count, which is an Integer. To resolve this error, either Dim Count As Variant, or assign a 0 (zero) to Count instead of Null.

Assign a 0 to Count and run the function again. The error is raised again because the function is evaluating a non-Variant data type to Null (For Count = 1 To Null).
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kberrmsg kbprb kbProgramming KB210471
©2004 Microsoft Corporation. All rights reserved.