ACC: Links to Formatted DDE Items Result in Text

• Microsoft Access versions 1.0, 1.1, 2.0

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" (without the quotation marks).

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

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