Excel displays incorrect values when you export an Access report with numbers (823222)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

You may use the Export option in Access to export a report to Microsoft Excel. If the report has numeric values in the text field, the resultant Excel worksheet may have incorrect values.

This problem may also occur when you use the Analyze It with Microsoft Excel option in Access.

WORKAROUND

To work around this problem, follow these steps:
  1. Concatenate a character to the numeric values that are in a text field. For example, you can concatenate an apostrophe (').
  2. Export the report to Excel.
  3. Remove the concatenated character from the field in Excel.
To work around the problem for the example that is provided in the "Steps to Reproduce the Problem" section, follow these steps:
  1. Start Access.
  2. Open the C:\MyExample.mdb database.
  3. In the Database window under Objects, click Reports.
  4. In the right-pane, right-click MyReport and then click Design View.
  5. In the Detail section, right-click the MyText text box and then click Properties.
  6. In the Text Box: myText dialog box, click the Data tab.
  7. In the Control Source box, type =[MyText] & "'".
  8. Click the All tab, and then change the value of the Name property to MyText1.
  9. Click the Print Preview icon.
  10. On the Tools menu, point to Office Links and then click Analyze It with Microsoft Excel.
You can see that the values in the MyText column display correctly. However, the values each have an apostrophe-character suffix.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To remove the apostrophe-character suffix from the MyText column in Excel, follow these steps:
  1. Select the columns that have the apostrophe-character suffix.
  2. On the Tools menu, point to Macro and then click Visual Basic Editor.
  3. In the Project - VBA Project window, click ThisWorkbook.
  4. On the View menu, click Code.
  5. Paste the following code in the Code window:
    Private Sub Workbook_Open()
  
    Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
  6. In the Code window, click any part of the code that is mentioned in step 5. On the Run menu, click Run Sub/UserForm.
  7. On the File menu, click Close and Return to Microsoft Office Excel.

    Note The apostrophe-character suffix is removed from the selected columns.

MORE INFORMATION

Steps to Reproduce the Problem

  1. Start Access.
  2. Create a new database and then save the database as C:\MyExample.mdb.
  3. Create a table that is named tblExample and then add records to the table.

    To do this, follow these steps:
    1. In the Database window under Objects, click Tables.
    2. On the File menu, click Save.
    3. In the Save As dialog box, type tblExample and then click OK.
    4. On the View menu, click Datasheet View and then add the following records under the MyText field:
      165-5
		99-3
		457-3
		689-54
    5. On the File menu, click Save.
  4. Create a report that is based on the tblExample table:

    To do this, follow these steps:
    1. In the Database window under Objects, click Reports and then click New.
    2. In the New Report dialog box, select Autoreport: Tabular. In the Choose the table or query where the object's data comes from, click tblExample.
    3. Click OK.
    4. On the File menu, click Save.
    5. In the Save As dialog box in the Report Name box, type MyReport.
  5. On the Tools menu, point to Office Links and then click Analyze It with Microsoft Excel.

    Note You may see the following values in the MyText column in the Excel worksheet:
    -633573
36220
-526983
689-54

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

REFERENCES

For more information about exporting Access reports to Excel, click Microsoft Access Help on the Help menu, type export data or database objects in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:9/28/2006
Keywords:kbReport kbfield kbDatabase kbbug KB823222 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.