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

ID: Q210471

The information in this article applies to:

Microsoft Access 2000

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).

Additional query words: pounderror prb

Keywords : kberrmsg kbprg kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb