Microsoft KB Archive/824277

= Error message when you try to open a query that contains a function: &quot;Undefined Function  in Expression&quot; =

Article ID: 824277

Article Last Modified on 7/23/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003

-



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.



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.

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  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  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:  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:   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 &quot;Applies to&quot; 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 &quot;Symptoms&quot; section. When you click OK, you receive the second error message that is mentioned in the &quot;Symptoms&quot; 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
 Create a new blank database in Access. Close the database that you created in step 1, and then open the Northwind sample database. Use the following Microsoft SQL statements to create two new queries in the Northwind sample database.   SELECT CustomerID, CompanyName, ContactName, ContactTitle, Trim([City]) & &quot;, &quot; & Trim([Region]) & &quot; &quot; & Trim([PostalCode]) AS CoAddress FROM Customers; Save this query as QryTest. </li>  SELECT CustomerID, Left([CompanyName],4) AS ShortName FROM Customers; Save this query as QryTest2. </li></ul> </li> 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.</li> Open the Utility Functions module in Design view.

Note In Access 2007, double-click the Utilities module.</li> On the Tools menu in the Visual Basic Editor, click References.</li> In the References - Northwind dialog box, click Browse.</li> 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.</li> Click the database that you created in step 1, and then click Open.</li> In the References dialog box, click OK, and then exit Access.</li> Open Windows Explorer, locate the folder where you put the database that you create in step 1, and then delete the database.</li> Reopen the Northwind sample database, and then try to open the QryTest query or the QryTest2 query. Notice that the following behavior occurs: <ul> 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.

Error message 2

To ensure that your database or project works properly, you must fix this reference.

</li> In Access 2003, you receive the following error message:

Undefined function  in expression.

</li></ul> </li> Click OK.

If you are using Access 2007, notice that you receive the following error message:

Undefined function  in expression.

</li></ol>

Additional query words: Acc2003 ACC2007

Keywords: kberrmsg kbtshoot kbbug kbprb kbexpertiseinter KB824277

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.