Microsoft KB Archive/161013

= ACC: Using Eval Function with Parameters in Parameter Queries =

Article ID: 161013

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q161013



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



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

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

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

</li></ol>

<div class="references_section">