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

• Microsoft Access version 2.0

SYMPTOMS

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

When you run an append or update query, you receive a "type conversion failure" error message when a query parameter is not explicitly declared. For example, an update query fails if its Update To parameter is a reference to a form control.

CAUSE

The query parameter is implicitly declared and, as a result, is automatically treated as text. The conversion fails, resulting in the error message. Note that the error message occurs only if you are using the Microsoft Jet database engine version 2.5, which is installed when you install the Microsoft Access version 2.0 Service Pack.

RESOLUTION

To resolve this problem, either wrap the parameter with a conversion function such as CVDate(), CLng(), or CCur(), or explicitly declare the parameter's data type in the Parameters dialog box. (To use the Parameters dialog box, choose Parameters from the Query menu.)

If you want to automate the process of explicitly defining parameter data types, you can install a utility application called Params.exe. For information on how to obtain Params.exe, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q149357
   TITLE     : ACC2: Parameter Typing Utility Available on MSL

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

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.

REFERENCES

For more information about parameters, search for "parameter query," and then "Creating a Parameter Query" using the Microsoft Access Help menu.

For information about the Microsoft Access version 2.0 Service Pack, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q122927
   TITLE     : WX1124: Microsoft Access Version 2.0 Service Pack