ACC: Links to Formatted DDE Items Result in Text

ID: Q90108

The information in this article applies to:

SYMPTOMS

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

A calculated control that uses another control linked through dynamic data exchange (DDE), displays the "#Error" 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("Excel","Sheet1","R1C1")


  10. Create a new text box on the form, and enter the formula:

    =Field0+100


  11. View the form in Form view. Note that the first text box, Field0, displays "$80." The second text box displays "#Error."


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("Excel","Sheet1","R1C1"))


  3. 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.


REFERENCES

For more information about data type conversions, search for "converting data types" using the Microsoft Access Help menu. 


Keywords          : kberrmsg kbinterop 
Version           : 1.0 1.1 2.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: March 10, 1999