ACC: "Expected Function or Variable" Error Message

ID: Q145705

The information in this article applies to:

Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run or compile a procedure in a Microsoft Access database that references a field with the same name as a data-access method on a Recordset object, you may receive one of the following error messages:

   Expected Function or variable

   -or-

   Argument not optional

   -or-

   Type Mismatch

CAUSE

You used the dot (.) syntax when you referenced a field with the same name as a data-access method on a Recordset object. For example, you may have a field called "Close" that is preceded with a dot:

   Function Test()
      Dim db As DATABASE, tb As Recordset, x As Variant
      Set db = CurrentDb
      Set tb = db.OpenRecordset("Table1")
      x = x + tb.[Close]
   End Function

RESOLUTION

Modify the procedure to use the exclamation point (!) syntax in the reference, for example:

   Function Test()
      Dim db As DATABASE, tb As Recordset, x As Variant
      Set db = CurrentDb
      Set tb = db.OpenRecordset("Table1")
      x = x + tb![Close]
   End Function

STATUS

This behavior is by design.

MORE INFORMATION

The particular error message that you receive when you run or compile a procedure depends on the assigned name of the referenced field as follows:

If you receive the "Expected Function or variable" error message, then your field has the same name as one of the following data access-methods for Recordset objects:

      AddNew
      CancelUpdate
      Close
      Delete
      Edit
      FillCache
      MoveFirst
      MoveLast
      MoveNext
      MovePrevious
      Requery
      Update

If you receive the "Argument not optional" error message, then your field has the same name as one of the following data-access methods for Recordset objects:
```
      FindFirst
      FindLast
      FindNext
      FindPrevious
      Move
      Seek
```
If you receive the "Type Mismatch" error message, then your field has the same name as one of the following data-access methods for Recordset objects:
```
      Clone
      CopyQueryDef
      OpenRecordset
```

Steps to Reproduce Behavior

1. Open a Microsoft Access database.

2. Create a new table named Table1 and add a field named Close.

3. Create a module and type the following procedure:

      Function Test()
        Dim db As DATABASE, tb As Recordset, x As Variant
        Set db = CurrentDb
        Set tb = db.OpenRecordset("Table1")
        x = x + tb.[Close]
      End Function

4. On the Run menu, click Compile All Modules. Note that you receive the

   following error message:

        Expected Function or variable

REFERENCES

For more information about naming conventions, search the Help Index for "naming conventions," or ask the Microsoft Access 97 Office Assistant.

Additional query words: argument not optional type mismatch

Keywords          : kberrmsg kbprg
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb

Last Reviewed: November 20, 1998