The information in this article applies to:
SYMPTOMSModerate: Requires basic macro, coding, and interoperability skills.
CAUSEBecause the numbers are being exported to a fixed-width text file, Microsoft Access pads zeros to the right of the number until it meets the proper field width. RESOLUTIONTo 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 DISTINCTROWFor this example, the SQL statement assumes that you have the following table:
It 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. This 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 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. MORE INFORMATIONSteps to Reproduce Behavior
34.5 $34.5000 REFERENCESAdditional query words: importing/exporting leading zeros prb
Keywords : kb3rdparty kbdta IsmTxtfx |
Last Reviewed: May 13, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |