ACC2: "Type Conversion Failure" with Update or Append Query

MORE INFORMATION

When you use a parameter in a query without defining the parameter's data type in the Parameters dialog box, the parameter is implicitly declared. Parameters whose data type is declared in the Parameters dialog box are explicitly declared. Parameters that return values from a conversion function are also considered to be explicitly declared.

This problem affects some of the append and update queries in the Microsoft Access Solutions Pack Asset Tracker application. For example, no reports are listed in the Select Reports form, and no assets are listed in the Reassign Assets form. The queries that fill the temporary tables on which these forms are based are failing.

Steps to Reproduce Problem

The following example uses a default date value in a text box on a form as a parameter for an update query:

1. Open the sample database NWIND.MDB.
2. Create the following new query based on the Employees table:
   

         Query: Updater
         --------------------------------
         Type: update query
         Field: Birth Date
            Table: Employees
            Update To: Forms!Test1!Field0
   						

3. Create a blank new form called Test1 and add the following controls to the form:
   

         Text Box
         ------------------------
            Name: Field0
            Format: Short Date
            Default Value: ="1/1/95"
   
         Command Button
         -----------------------------
            Name: Button0
            Caption: Run Updater Query
   						

4. Set the command button's OnClick property to the following event procedure:
   

         Sub Button0_Click ()
            DoCmd OpenQuery "Updater"
         End Sub
   						

5. View the form in Form view and choose the command button. When you are prompted "Update query will modify data. Continue anyway?" choose OK.
6. When you are prompted "15 row(s) will be updated" choose OK.
7. When you are prompted
   

         Errors were encountered: 15 fields were not updated due to type
         conversion failure, 0 record(s) were not updated due to key
         violations, and 0 record(s) were not updated due to lock
         violations. Continue anyway?
   						

   
   Choose Cancel.

Steps to Correct the Problem Using an Explicit Parameter

The following examples show how to correct the problem demonstrated by the example in the "Steps to Reproduce Problem" section above.

CAUTION: Following the steps in these examples will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

Method 1

1. Open the Updater query in Design view. From the Query menu, choose Parameters.
2. In the Parameters dialog box, add the following entry:
   

         Parameter: Forms!Test1!Field0
         Data Type: Date
   						

3. Close the Parameters dialog box.
4. Save the query and then run it. The query should run correctly.

Method 2

1. Open the Updater query in Design view.
2. Change the query's Update To expression to be:
   

         CVDate(Forms!Test1!Field0)
   						

3. Save the query and then run it. The query should run correctly.