ACC: Using Eval Function with Parameters in Parameter Queries

MORE INFORMATION

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.