ACC2: "Type Conversion Failure" with Update or Append Query (125259)


The information in this article applies to:

  • Microsoft Access 2.0
This article was previously published under Q125259

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:

149357 ACC2: Parameter Typing Utility Available in Download Center

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:

122927 WX1124: Microsoft Access Version 2.0 Service Pack
Modification Type:MajorLast Reviewed:11/6/2000
Keywords:kbbug kbusage KB125259
©2002 Microsoft Corporation. All rights reserved.