Microsoft KB Archive/288572

= ACC2002: Exporting to Fixed-Width Text File Left-Aligns Numbers =

Article ID: 288572

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q288572



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

For a Microsoft Access 2000 version of this article, see 201115.



SYMPTOMS
When you export a table that contains a Number or a Currency field to a fixed-width text file, the numbers in the table become left-aligned.



CAUSE
Because the numbers are being exported to a fixed-width text file, Microsoft Access pads spaces to the right of the number until it meets the proper field width.



RESOLUTION
To make the numbers align with the decimal and pad zeros to the left instead of to the right, you must first determine the maximum length of the Number or Currency field. Use this number of zeros in the Format function below. Next, you must determine the number of decimal places that the field in question uses. Use this value to determine how many zeros to place after the decimal point in the Format function below. Finally, create a query based on the following Microsoft Access SQL statement:

SELECT DISTINCTROW

Format([MyNumber],&quot;0000.00&quot;) AS [Expr1]

INTO [MyNewTable]

FROM [MyOldTable];

For this example, the SQL statement assumes that you have the following table:   Table: MyOldTable -  Field Name: MyNumber Data Type: Currency The SQL statement also assumes that the longest value in the MyNumber field is 7 characters (including the decimal point and decimal places) and that each value has two decimal places. When you type this Microsoft Access SQL statement in the SQL window in the query design grid, it creates a new table named MyNewTable with the MyNumber field padded to the left with zeros.

When you use this Microsoft Access SQL statement in a query, the data from MyOldTable is formatted with decimal justification and placed in a new table called MyNewTable.



Steps to Reproduce the Behavior
  Create the following table:      Table: MyOldTable -     Field Name: MyNumber Data Type: Currency  Enter the following numbers in the table:

34.5

123.56

4578.90

 On the File menu, click Export and select the Text Files option. When the Export Text Wizard prompts you, click Fixed Width, and then click Finish. Open the exported text file in any text editor, such as Notepad, and view the numbers. Note that they appear as follows:

$34.50

$123.56

$4578.90

</li></ol>

Additional query words: importing/exporting leading zeros prb

Keywords: kbprb kb3rdparty KB288572

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.