Microsoft KB Archive/135547

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

Article ID: 135547

Article Last Modified on 7/5/2002

-

APPLIES TO


 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q135547



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



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



Steps to Reproduce Behavior
  Create a new, blank table with the following structure:

     Table: Table1 Field Name: ID        Data Type: AutoNumber Field Name: TestNumber Data Type: Text  Save the table as Table1, and then view the table in Datasheet view.  Type the following values in the TestNumber field:

     0012.340 0043.210     0056.780      0087.650                         Close the Table1 table. Create a new report based on the Table1 table using the AutoReport: Tabular Wizard.</li> On the File menu, click Print Preview.</li> 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.</li>  As Microsoft Excel automatically starts and opens the Report1.xls file, note that the leading and trailing zeroes are removed, for example:

<pre class="fixed_text">     12.34 43.21     56.78      87.65                        </li></ol>

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.

<div class="references_section">