Microsoft KB Archive/194374

= ACC97: Error Message: Function Isn't Available in Expressions in Query Expression =

Article ID: 194374

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition
 * Microsoft Office 97 Developer Edition

-



This article was previously published under Q194374



Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
When you run a query, you may receive the following error message:

Function isn't available in expressions in query expression.

You may see a "#Name" error on forms and reports in controls that use an expression for the ControlSource property. This behavior can occur on forms, on queries, or on reports that use built-in functions in expressions, for example, the Format, Left, or Right function.



CAUSE
You developed a database by using a particular version of a type library, and then upgraded the version of the type library on your development computer.

-or-

You referenced a type library on your development computer, and then moved the database to a computer that does not have the library, or has a newer version of the library.

NOTE: This issue often involves references to Comctl32.ocx. But this issue can also occur with other type libraries. An example of installing a newer type library version is installing newer versions of Microsoft Data Access Components (MDAC). Sometimes the upgrade of a type library is subtle, such as when you upgrade the operating system version or install a new application.



RESOLUTION
To fix this problem, you must have the same version of the type library on your development computer and on other computers that will be using the database. Or you must refresh the reference to the type library on the computers that will be using the database. You must do this so that Microsoft Access can recognize the control. What follows are three methods that you can use to do this.

IMPORTANT NOTE ON MDE FILES: The following methods work only in standard Access files (.mdb). You cannot refresh references in an Access MDE (.mde) file. References must be refreshed in the original .mdb file before being recompiled into a new .mde.

Using Microsoft Access to Refresh a Reference
You can use Microsoft Access to refresh the references on a target computer manually. To do so, follow these steps:
 * 1) Open the database that causes the errors mentioned in the "Symptoms" section.
 * 2) On the Modules tab, click New.
 * 3) On the Tools menu, click References.
 * 4) In the References dialog box, pick any single reference that is not already selected, click to select it, note which one you selected, and then click OK.
 * 5) On the Tools menu, click References again.
 * 6) Click to clear the reference that you selected in step 4, and then click OK.
 * 7) Run the query, the form, or the report on which you saw the errors mentioned in the "Symptoms" section. No errors occur.

Updating the Distributed Control
You can update the version of the control on your development computer to match the one on your target computer. After you have updated the control on your development computer and refreshed the reference to it in your database (see the "Using Microsoft Access 97 to Refresh a Reference" section earlier in this article), you can copy the database back onto the target computers.

If you are using the Microsoft Office 97 Developer Edition (ODE) to distribute an application, run the Setup Wizard again to rebuild the setup files, and then have users reinstall the application.

Automatically Refreshing References by Using Visual Basic for Applications
You can use Visual Basic for Applications code to refresh the references in your database automatically. This solution uses a query to test for the problem described by this article, and then runs Visual Basic for Applications code to refresh the references if the problem exists. If you are distributing ODE applications, you can include this in both future and upgrade versions of your application.   Create a new query in your database to test for the symptoms. The query needs to use one of the built-in Access functions, for example the Format, Left, or Right function. Save the query as qryTestRefs.

For example, using the Northwind database, you could use a query that returns the first letter of the Category Name field in the Category table, as follows:   Query: qryTestRefs --

Field: Expr1 Value: Left([Category Name],1)   Create a macro as follows, and save it as AutoExec:   Macro Name   Action AutoExec    RunCode

Action Arguments -

Function Name: CheckRefs  Create the following module, and type or paste the following code into the new module:

Function CheckRefs Dim db As Database, rs As Recordset Dim x  Set db = CurrentDb

On Error Resume Next

' Run the query qryTestRefs you created and trap for an error. Set rs = db.OpenRecordset("qryTestRefs", dbOpenDynaset)

' The if statement below checks for error 3075. If it encounters the ' error, it informs the user that it needs to fix the application. ' Error 3075 is the following: ' "Function isn't available in expressions in query expression..."

' Note: This function only checks for the error 3075. If you want it to  ' check for other errors, you can modify the If statement. To have ' it check for any error, you can change it to the following: ' If Err.Number <> 0

If Err.Number = 3075 Then MsgBox "This application has detected newer versions " _ & "of required files on your computer. " _ & "It may take several minutes to recompile " _ & "this application." Err.Clear FixUpRefs End If

End Function

Sub FixUpRefs Dim loRef As Access.Reference Dim intCount As Integer Dim intX As Integer Dim blnBroke As Boolean Dim strPath As String

On Error Resume Next

'Count the number of references in the database intCount = Access.References.Count 'Loop through each reference in the database 'and determine if the reference is broken. 'If it is broken, remove the Reference and add it back. For intX = intCount To 1 Step -1 Set loRef = Access.References(intX) With loRef blnBroke = .IsBroken If blnBroke = True Or Err <> 0 Then strPath = .FullPath With Access.References .Remove loRef .AddFromFile strPath End With End If      End With Next Set loRef = Nothing ' Call a hidden SysCmd to automatically compile/save all modules. Call SysCmd(504, 16483) End Sub



STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97.

<div class="moreinformation_section">

MORE INFORMATION
When a type library is installed on a computer, specific information about it is placed in the registry. Microsoft Access uses this registry information to reference and use the type library. Under normal conditions, installing a newer version of a type library on a computer has no effect on the programs that use it, but in some cases, changes to the type libraries cause them to be registered differently. When this happens, Microsoft Access cannot use them until the new registry information is refreshed in the References collection.

Steps to Reproduce Problem
 Reformat two test computers.</li> Install Microsoft Windows 95 or Microsoft Windows 98 on both computers.</li> On the first computer, install Microsoft Access 97.</li> On the first computer, install Microsoft Office 97 Developer Edition.</li> On the first computer, create a new database.</li> In the new database, click Tables, click New, and then click Design View.</li>  In Design view, create the following new table: <pre class="fixed_text">       Table: Table1 ---       Field Name: CategoryID Data Type: Counter Indexed: Yes (No Duplicates) Field Name: CategoryName Data Type: Text Field Name: Location Date Type: Text Field Size: 20 Indexed: Yes (Duplicates OK)

Table Properties: Table1 --       PrimaryKey: CategoryID Index1: CategoryName; Location </li> Save the table as Table1, and then switch to Datasheet view.</li> In Datasheet view, add a few records to the table.</li> Create a new query based on the table.</li>  Add the Field1 field to the query, as well as the following expression: <pre class="fixed_text">       EXPR1: Format([CategoryID], " #") </li> Save the query as Query1, and then close it. Do not run the query.</li> Open a new, blank form in Design view.</li> Insert the Microsoft CommonDialog (version 5.0) and the Microsoft Treeview (version 5.0) controls on the form.</li> On the View menu, click Code to see the module of the form. (This automatically sets the HasModule property to Yes.)</li> <li> On the Tools menu, click References, and verify that you have the following references (in order): <pre class="fixed_text">       Visual Basic For Applications Microsoft Access 8.0 Object Library Microsoft DAO 3.5 Object Library Microsoft Common Dialog Control 5.0 Microsoft Windows Common Controls 5.0 </li> <li>On the Debug menu, click Compile And Save All Modules. Save the form as Form1 when you are prompted.</li> <li>Close the form.</li> <li>Run the Query1 query and verify that it works correctly.</li> <li>Copy the database to a second computer that has version 5.00.4319 or later of COMCTL32.OCX installed. This version of the control can be obtained with Microsoft Visual Studio 5.0 SP3 or Visual Studio 6.0.</li> <li>Open the database on the second computer and run the Query1 query. Note that you receive the following error message:

Function isn't available in expressions in query expression 'Format([CategoryID], " #")'

</li></ol>

Following this error message, if you change the references in any way, the problem is resolved. For example, you can add a reference at random, click OK to close the References dialog box, and then remove that reference.

<div class="references_section">