ACC97: Error Message: Function Isn't Available in Expressions in Query Expression (194374)

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.

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