ACC: Using Eval Function with Parameters in Parameter Queries

ID: Q161013


The information in this article applies to:
  • 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.
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:

  1. Open the sample database Northwind.mdb.


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


  3. 
          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") 
  4. Save the QryEval query and close it.


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


  6. 
          Form: FrmEval
          -----------------------------
          Caption: Eval Form
    
          Text box:
             Name: Text0
          Command button:
             Name: Command0
             Caption: Run Query
             OnClick: [Event Procedure] 
  7. 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:


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


  10. 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:
  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:


  6. 1 parameters were expected, but only 0 were supplied.


REFERENCES

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

Additional query words:

Keywords : kbusage AccCon QryParm
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: October 6, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.