Article ID: 192041 - View products that this article applies to.
This article was previously published under Q192041
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.
When you import data into Microsoft Access, trailing zeros may be lost. This will happen when you import data that is formatted to show these zeros, but where the zeros are not actually part of the data. For example, in a Microsoft Excel workbook, you can format the number 1234 so that it will be displayed as 1234.000. When you import this workbook into a Microsoft Access table, the number will be displayed as 1234. This article shows you how to preserve trailing zeros when you import data into Microsoft Access.
The following example illustrates how to import formatted numeric data from a Microsoft Excel worksheet into a Microsoft Access database, and then how to create a query that filters the numbers.
Creating the Import Data FileThis section shows you how to create a Microsoft Excel worksheet that has numbers formatted to contain trailing zeros in each cell. It then shows you how to save the worksheet to a text file to retain the trailing zeros. These trailing zeros are part of the number format, not part of the data itself.
Importing the DataThis section shows you how to import the data into Microsoft Access.
NOTE: The numbers in the new table will appear the same as they did in the Excel workbook.
Using the Numeric Value of the DataThe following example shows how you can use the CVar() function to have the contents of the field be evaluated as a number data type rather than as a text data type. Because the data is in a text format, you may not receive the expected results when running the query, unless you use the numeric value of the number.
Field: RealNumbers: CVar([TextNumbers])*1
For more information about data types, click Contents And Index on the Help menu, click the Index tab in Microsoft Access Help, type the following text data type and then double-click the selected text to go to the "Type Conversion Functions" topic. If you are unable to find the information you need, ask the Office Assistant.
Article ID: 192041 - Last Review: October 10, 2013 - Revision: 2.1