ACC: Links to Formatted DDE Items Result in Text

Last reviewed: June 6, 1997
Article ID: Q90108
The information in this article applies to:
  • 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."

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 : IntpDde kberrmsg kbinterop
Version : 1.0 1.1 2.0
Platform : WINDOWS
Hardware : x86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 6, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.