ACC: Using Eval Function with Parameters in Parameter Queries (161013)

SUMMARY

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.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

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:

Open the sample database Northwind.mdb.

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

Save the QryEval query and close it.

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]

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

Save the FrmEval form, and then open it in Form view.
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.

If you want to further test what happens when you do not use the Eval() function and you do not explicitly declare the query parameter, follow these steps:

Open the QryEval query in Design view.
Change the OrderDate (or Order Date in version 2.0) criteria to [Forms]![FrmEval]![Text0].
Save the query and close it.
Open the FrmEval form in Form view.
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.

ACC: Using Eval Function with Parameters in Parameter Queries (161013)

SUMMARY

MORE INFORMATION

REFERENCES