ACC95: Analyze It With MS Excel Drops Leading/Trailing Zeroes

ID: Q135547

The information in this article applies to:

SYMPTOMS

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

When you use the Analyze It With Microsoft Excel command to output a report to a Microsoft Excel (.xls) file, any leading or trailing zeroes stored in a text field are dropped. For example, the text value "0012.3400" is output as "12.34."


RESOLUTION

You can set the Format property of the report control that is bound to the text field containing the leading and trailing zeros to as many zeros as needed, for example, 0000.00. The Format pattern specified for that control will be used to format the data in Microsoft Excel. However, the actual data exported will not contain leading or training zeros.


MORE INFORMATION

Steps to Reproduce Behavior


  1. Create a new, blank table with the following structure: 
    
      Table: Table1
      ------------------------
      Field Name: ID
         Data Type: AutoNumber
      Field Name: TestNumber
         Data Type: Text


  2. Save the table as Table1, and then view the table in Datasheet view.


  3. Type the following values in the TestNumber field: 
    
      0012.340
      0043.210
      0056.780
      0087.650


  4. Close the Table1 table.


  5. Create a new report based on the Table1 table using the AutoReport: Tabular Wizard.


  6. On the File menu, click Print Preview.


  7. On the Tools menu, click Office Links, and then click Analyze It With Microsoft Excel. Note that Microsoft Access automatically outputs the report to a field called Report1.xls.


  8. As Microsoft Excel automatically starts and opens the Report1.xls file, note that the leading and trailing zeroes are removed, for example: 
    
      12.34
      43.21
      56.78
      87.65


If the Format property of the TestNumber field in the report created in step 5 is set to 0000.000, the data displayed in Microsoft Excel will match the format of step 3. However, the actual data in each cell will still match the output shown in step 8.


REFERENCES

For more information about the Format property, search for "format," and then "Format Property," using the Microsoft Access Help Index. 


Keywords          : kbinterop IntpOff IsmExl5 
Version           : 7.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 30, 1999