ACC: Using Eval Function with Parameters in Parameter Queries

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

If you use the Eval() function with a parameter expression in the criteria of a query, you do not need to explicitly declare the parameter in order to run the query in code.

If you do not use the Eval() function and you do not explicitly declare the parameter, you may receive the following error message when you run the query in code, where n represents a number of parameters.

In Microsoft Access 7.0 and 97

      Run-time error 3061:
      Too few parameters. Expected n.

In Microsoft Access 2.0

      n parameters were expected, but only 0 were supplied.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

The following example demonstrates how to use the Eval() function with a parameter expression in a query:

1. Open the sample database Northwind.mdb.

2. Create a new query based on the Orders table:

         Query: QryEval
         -----------------------------------------------
         Type: Select Query
   

         Field: OrderID (or Order ID in version 2.0)
            Table: Orders
         Field: OrderDate (or Order Date in version 2.0)
            Table: Orders
            Criteria: =Eval("Forms!FrmEval!Text0")
   
   

3. Save the QryEval query and close it.

4. Create a new form not based on any table or query in Design view:

         Form: FrmEval
         -----------------------------
         Caption: Eval Form
   

         Text box:
            Name: Text0
         Command button:
            Name: Command0
            Caption: Run Query
            OnClick: [Event Procedure]
   
   

5. Set the OnClick property of the command button to the following event procedure.

   In Microsoft Access 7.0 and 97:

         Private Sub Command0_Click()
            Dim MyDB As Database
            Dim MySet As RecordSet
            Set MyDB = CurrentDb()
            Set MySet = MyDB.OpenRecordSet("QryEval")
            MySet.MoveFirst
            MsgBox MySet!OrderID
            MySet.Close
         End Sub
   
      In Microsoft Access 2.0:
   
         Sub Command0_Click()
            Dim MyDB As Database
            Dim MySet As RecordSet
            Set MyDB = CurrentDb()
            Set MySet = MyDB.OpenRecordSet("QryEval")
            MySet.MoveFirst
            MsgBox MySet![Order ID]
            MySet.Close
         End Sub
   
   

6. Save the FrmEval form, and then open it in Form view.

7. In Microsoft Access 7.0 and 97, type 8/4/94 in the text box, and then click the Run Query button. In Microsoft Access 2.0, type 7/5/93 in the text box, and then click the Run Query button. Note that a message box appears that displays an Order ID number.

1. Open the QryEval query in Design view.

2. Change the OrderDate (or Order Date in version 2.0) criteria to [Forms]![FrmEval]![Text0].

3. Save the query and close it.

4. Open the FrmEval form in Form view.

5. In Microsoft Access 7.0 and 97, type 8/4/94 in the text box, and then click the Run Query button. In Microsoft Access 2.0, type 7/5/93 in the text box, and then click the Run Query button.

   Note that in Microsoft Access 7.0 and 97, you receive the following error message:

         Run-time error 3061:
         Too few parameters. Expected 1.
   

   In Microsoft Access 2.0, you receive the following error message:

         1 parameters were expected, but only 0 were supplied.
   

REFERENCES

For more information about parameter queries, search the Help Index for "parameter queries."

Keywords          : kbusage SynFnc QryParm
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto