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

• Microsoft Access version 7.0

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
   

REFERENCES