ACC97: "Function isn't available in expressions..." Error Message

• Microsoft Access 97
• Microsoft Office Developer Edition 97

SYMPTOMS

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

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

Function isn't available in expressions in query expression.

CAUSE

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

NOTE: This issue most frequently involves references to COMCTL32.OCX, but can occur with other type libraries.

RESOLUTION

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

Using Microsoft Access to Refresh a Reference

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 click OK.



7. Run the query, form, or report on which you got the errors mentioned in the "Symptoms" section. The errors should not occur.

Updating the Distributed Control

Automatically Refreshing References by Using Visual Basic for Applications

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

2. Create a macro as follows, and save it as AutoExec:




      Macro Name   Action
      --------------------
      AutoExec     RunCode

      Action Arguments
      -------------------------

      Function Name: CheckRefs() 

3. Create the following module, and type or paste the following code into it:




      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)
         x=rs!expr1

         ' If you see error 3075, inform the users you have to fix
         ' the application.  Error 3075 is the following:
         ' Function isn't available in expressions in query expression...

          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 r As Reference, r1 As Reference
         Dim s As String

         ' Look for the first reference in the database other
         ' than Access and Visual Basic for Applications.
         For Each r In Application.References
            If r.Name <> "Access" And r.Name <> "VBA" Then
               Set r1 = r
               Exit For
            End If
         Next
         s = r1.FullPath

         ' Remove the Reference and add it back.
         References.Remove r1
         References.AddFromFile s

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

MORE INFORMATION

When a type library is installed on a computer, specific information about it is placed in the Windows 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 can't use them until the new registry information is refreshed in the References collection.

Steps to Reproduce Problem

1. Reformat two test computers.



2. Install Microsoft Windows 95 or Microsoft Windows 98 on both computers.



3. On the first computer, install Microsoft Access 97.



4. On the first computer, install Microsoft Office 97 Developer Edition.



5. On the first computer, create a new database.



6. In the new database, on the Tables tab, click New, and then click Design View.



7. In Design view, create the following new table:




        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 

8. Save the table as Table1 and switch to Datasheet view.



9. In Datasheet view, add a few records to the table.



10. Create a new query based on the table.



11. Add Field1 to the query, as well as the following expression:




        EXPR1: Format([CategoryID], " #") 

12. Save the query as Query1, and then close it. Do not run the query.



13. Open a new, blank form in Design view.



14. Insert the Microsoft CommonDialog (version 5.0) and the Microsoft Treeview (version 5.0) controls on the form.



15. On the View menu, click Code to see the module of the form. (This automatically sets the HasModule property to Yes.)



16. On the Tools menu, click References, and verify that you have the following references (in order):




        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 

17. On the Debug menu, click Compile And Save All modules. Save the form as Form1 when prompted.



18. Close the form.



19. Run the Query1 query and verify that it works correctly.



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



21. 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], " #")' 

REFERENCES

For more information about references, search the Help Index for "References collection," or ask the Microsoft Access 97 Office Assistant.

Additional query words: typelib activex

Keywords          : kberrmsg kbdta IntpCstm OdeGen 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbbug 

Last Reviewed: May 13, 1999