Error message when you try to open a query that contains a function: "Undefined Function <FunctionName> in Expression"

Article translations Article translations
Article ID: 824277 - View products that this article applies to.
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.
For a Microsoft Access 2002 version of this article, see 275110.
Expand all | Collapse all

On This Page

SYMPTOMS

In Microsoft Office Access 2007, when you try to open a query that contains a function, you receive one of the following error messages:
Error message 1
Your Microsoft Access database or project contains a missing or broken reference to the file FileName.
Error message 2
To ensure that your database or project works properly, you must fix this reference.
When you click OK, you receive the following error message:
Undefined function FunctionName in expression.
In Microsoft Office Access 2003, when you try to open a query that contains a function, you receive the following error message:
Undefined function FunctionName in expression.

CAUSE

The database contains a reference to the database, to the type library, or to the object library that is marked as missing in the MISSING: ReferenceName check box. This check box appears in the References dialog box.

WORKAROUND

To work around this issue, remove the reference that is marked as missing. To do this, follow these steps:
  1. Start Access.
  2. Open the database that contains the missing or broken reference.
  3. Press ALT+F11 to open the Visual Basic Editor.
  4. On the Tools menu in the Visual Basic Editor, click References.
  5. Click to clear the MISSING: ReferenceName check box for the type library or for the object library.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

When you open a database in Access 2007 or in Access 2003, you are informed about any problems with referencing external libraries and projects. When the database has a broken reference to a library or to a project at the time that you try to run a query that calls a function, you receive the first error message that is mentioned in the "Symptoms" section. When you click OK, you receive the second error message that is mentioned in the "Symptoms" section.

You receive the first error message one time. If you have other queries that have calls to functions, you receive only the second error message when you run the other queries. This behavior occurs until you close and then reopen the database.

Caution If you follow the steps in this sample, you modify the Northwind sample database. You must back up the Northwind sample database, and then follow these steps on a copy of the Northwind sample database.

Steps to reproduce the problem

  1. Create a new blank database in Access.
  2. Close the database that you created in step 1, and then open the Northwind sample database.
  3. Use the following Microsoft SQL statements to create two new queries in the Northwind sample database.
    • SELECT CustomerID, CompanyName, ContactName, ContactTitle, Trim([City]) 
      	& ", " & Trim([Region]) & " " & Trim([PostalCode]) AS CoAddress FROM 
      	Customers;
      Save this query as QryTest.
    • SELECT CustomerID, Left([CompanyName],4) AS ShortName FROM Customers;
      
      Save this query as QryTest2.
  4. On the View menu, point to Database Objects, and then click Modules.

    Note If you use Access 2007, click Supporting Objects in the left pane.
  5. Open the Utility Functions module in Design view.

    Note In Access 2007, double-click the Utilities module.
  6. On the Tools menu in the Visual Basic Editor, click References.
  7. In the References - Northwind dialog box, click Browse.
  8. Select Microsoft Access Databases (*.mdb) in the Files of type list, and then locate the folder where you put the database that you created in step 1.

    Note In Access 2007, select Microsoft Access Databases (*.accdb) in the Files of type list, and then locate the folder where you put the database that you create in step 1.
  9. Click the database that you created in step 1, and then click Open.
  10. In the References dialog box, click OK, and then exit Access.
  11. Open Windows Explorer, locate the folder where you put the database that you create in step 1, and then delete the database.
  12. Reopen the Northwind sample database, and then try to open the QryTest query or the QryTest2 query. Notice that the following behavior occurs:
    • In Access 2007, you receive one of the following error messages:
      Error message 1
      Your Microsoft Access database or project contains a missing or broken reference to the file FileName.
      Error message 2
      To ensure that your database or project works properly, you must fix this reference.
    • In Access 2003, you receive the following error message:
      Undefined function FunctionName in expression.
  13. Click OK.

    If you are using Access 2007, notice that you receive the following error message:
    Undefined function FunctionName in expression.

Properties

Article ID: 824277 - Last Review: September 17, 2011 - Revision: 4.0
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
Keywords: 
kbprb kbtshoot kbexpertiseinter kberrmsg KB824277

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com