Knowledge Base

ACC: "Expected Function or Variable" Error Message

Article ID: 145705

Article Last Modified on 1/19/2007

APPLIES TO

This article was previously published under Q145705
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

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 kbprb kbprogramming KB145705


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.