Microsoft KB Archive/125259

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

Article ID: 125259

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



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:

 Open the sample database NWIND.MDB.  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   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   Set the command button's OnClick property to the following event procedure:

Sub Button0_Click DoCmd OpenQuery "Updater" End Sub </li> View the form in Form view and choose the command button. When you are prompted "Update query will modify data. Continue anyway?" choose OK.</li> When you are prompted "15 row(s) will be updated" choose OK.</li>  When you are prompted

<pre class="fixed_text">     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. </li></ol>

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
<ol> Open the Updater query in Design view. From the Query menu, choose Parameters.</li>  In the Parameters dialog box, add the following entry:

<pre class="fixed_text">     Parameter: Forms!Test1!Field0 Data Type: Date </li> Close the Parameters dialog box.</li> Save the query and then run it. The query should run correctly.</li></ol>

Method 2
<ol> Open the Updater query in Design view.</li>  Change the query's Update To expression to be:

<pre class="fixed_text">     CVDate(Forms!Test1!Field0) </li> Save the query and then run it. The query should run correctly.</li></ol>

<div class="references_section">