ACC: Exporting to Fixed-Width Text File Left-Aligns Numbers

Last reviewed: June 10, 1997
Article ID: Q98663
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

When you export a table containing a Number or 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 zeroes 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 the right, you first must 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],"0000.00")
   AS [Expr1]
   INTO [MyNewTable]
   FROM [MyOldTable];

For this example, this SQL statement assumes that you have the following table:

   Table: MyOldTable
   -----------------
   Field Name: MyNumber
   Data Type: Currency

It also assumes that the longest value in the MyNumber field is 7 characters in length (including the decimal and decimal places) and that each value has two decimal places. The above Microsoft Access SQL statement, when typed in the SQL window in the query-by-example (QBE) grid, makes a new table called MyNewTable with the MyNumber field padded to the left with zeros.

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

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create the following table:

          Table: MyOldTable
          -----------------
          Field Name: MyNumber
          Data Type: Currency
    

  2. Enter the following numbers in the table:

          34.5
          123.56
          4578.90
    

  3. On the File menu, click Export and select the Text (Fixed Width) option. Export MyOldTable.

  4. Open the exported text file in any text editor, such as Notepad, and view the numbers. Note that they appear as follows:

          34.5000
          123.560
          4578.90
    

REFERENCES

For more information about aligning fields in a fixed-width text file, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: ID: Q150401
   TITLE     : ACC: Exporting Right-Aligned Fields to a Text File (95/97)

   ARTICLE-ID: ID: Q148444
   TITLE     : ACC: Exporting Right-Aligned Fields to a Text File (1.x,
               2.0)


Additional query words: importing/exporting leading zeros
Keywords : IsmTxtfx kb3rdparty
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 10, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.