BUG: You receive an "Undefined Function in Expression" error message when you open a query that references a function (275110)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q275110
For a Microsoft Office Access 2003 version of this article, see 824277.
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you try to open a query that contains a function, you receive both of the following error messages. When you open any subsequent query, you receive only the second error.
Your Microsoft Access database or project contains a missing or broken reference to the file '<FileName>'.
  • To ensure that your database or project works properly, you must fix this reference.
  • To learn how to fix this reference, click Help.
when you click OK to the first error message, you receive a second message:
Undefined function '<FunctionName>' in expression.

CAUSE

Your database contains a reference to a database, type library, or object library that is marked as MISSING: <referencename> in the References dialog box.

RESOLUTION

To remove the missing reference, follow these steps:
  1. Open your database.
  2. Press ALT+F11 to open the Visual Basic Editor.
  3. On the Tools menu, click References.
  4. Click to clear the check box for the type library or object library marked as MISSING: <referencename>.
An alternative to removing the reference is to restore the referenced file to the path that is specified in the References dialog box. If the referenced file is in a new location, clear the MISSING: <referencename> reference, and then create a new reference to the file in the new folder.

STATUS

Microsoft has confirmed that this is a problem in Access.

MORE INFORMATION

When you open a database in Microsoft Access 2002, you are informed about any problems with referencing external libraries and projects. When the database contains a broken reference to a library or project at the time that you try to run a query that calls a function, Access 2002 displays the first message that is mentioned in the "Symptoms" section of this article. After you click OK to this message, you immediately receive the second message that is mentioned in the "Symptoms" section of this article.

After you receive the first message, the first message is not displayed again. Even if you have other queries that contain calls to functions, these other queries only return the second message until you close the database and then reopened it.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Steps to Reproduce the Behavior

  1. Start Access 2002, and then click Blank Database in the task pane.
  2. Click Create to create a new database that is named db1.mdb. Note where the database is being stored.
  3. Close db1.mdb, and then open the sample database Northwind.mdb.
  4. Use the following SQL statements to create two new queries. Save the first query as qryTest and the second query as qryTest2:

    SELECT CustomerID, CompanyName, ContactName, ContactTitle, Trim([City]) & ", " & Trim([Region]) & " " & Trim([PostalCode]) AS CoAddress FROM Customers;

    SELECT CustomerID, Left([CompanyName],4) AS ShortName FROM Customers;

    Test both queries to make sure they run without errors.
  5. On the View menu, point to Database Objects, and then click Modules.
  6. Open the Utility Functions module in Design view, and then click References on the Tools menu.
  7. In the References - Northwind dialog box, click Browse. Click Microsoft Access Databases (*.mdb) in the Files of type list, and then browse to the folder where you stored db1.mdb. Click the file name, and then click Open.
  8. Click OK in the References dialog box, and then quit Microsoft Access. Save the changes if you are prompted.
  9. Open Windows Explorer, and then browse to the folder where you stored db1.mdb. Delete the file.
  10. Reopen Northwind.mdb, and then try to open the qryTest query in Datasheet view. Note that you receive the first error message that is mentioned in the "Symptoms" section of this article.
  11. Click OK in the error message box, and note that you receive the second error message.
  12. Click OK in the second error message box, and then try to open the qryTest2 query. Note that you receive only the second error message this time.

REFERENCES

For additional information about another problem that could cause the first error message, click the following article number to view the article in the Microsoft Knowledge Base:

283806 Visual Basic for Applications (VBA) functions break in a database with missing references

Modification Type:MajorLast Reviewed:8/11/2004
Keywords:kbfunctions kbDatabase kbdesign kbQuery kbtshoot kbbug kberrmsg kbnofix KB275110
©2004 Microsoft Corporation. All rights reserved.