Microsoft KB Archive/90108

= ACC: Links to Formatted DDE Items Result in Text =

Article ID: 90108

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q90108



SYMPTOMS
Advanced: Requires expert coding, interoperability, and multiuser skills.

A calculated control that uses another control linked through dynamic data exchange (DDE), displays the &quot;#Error&quot; message when displayed in Form view or in a report's Preview.



CAUSE
Controls based on DDE links to numeric items that have been formatted in Microsoft Excel result in text. To perform calculations on the results of these links, you must convert the results to a Numeric data type.



RESOLUTION
The following example demonstrates how to create a DDE link to a numeric item that has been formatted:


 * 1) Start Microsoft Excel.
 * 2) In cell A1 of Sheet1, type 80.
 * 3) Select cell A1, and choose Number from the Format menu.
 * 4) From the Format Codes list, select a currency format, such as $#,##0_);($#,##0), and then choose the OK button.
 * 5) Select cell A1, and choose Copy from the Edit menu.
 * 6) Start Microsoft Access and open any database.
 * 7) Create a new form not based on any table or query.
 * 8) From the Edit menu, choose Paste Special, and then select Text from the Data Type list.
 * 9) Choose Paste Link. Note that a text box with the following formula is created on the form:

=DDE(&quot;Excel&quot;,&quot;Sheet1&quot;,&quot;R1C1&quot;)
 * 1) Create a new text box on the form, and enter the formula:

=Field0+100
 * 1) View the form in Form view. Note that the first text box, Field0, displays &quot;$80.&quot; The second text box displays &quot;#Error.&quot;

The following steps demonstrate how to convert the DDE-linked item to a Numeric data type:


 * 1) View the form in Design view.
 * 2) Change Field0 as follows:

=CCur(DDE(&quot;Excel&quot;,&quot;Sheet1&quot;,&quot;R1C1&quot;))
 * 1) View the form in Form view. Note that the second text box now displays the value 180.



STATUS
This behavior no longer occurs when you link formatted numbers between Microsoft Access version 7.0 and Microsoft Excel versions 5.0 or 7.0.

