Import from Excel - Access Adds Extra Decimal Places?

[Guest]

Greetings forum members,

As you have gathered from my subject line, the problem I am experiencing is
that MS Access is adding extra decimal places (precision) to numeric values
that I am importing from Excel. To illustrate, the value 622.9 (in Excel) is
stored as 622.900024414063 in MS Access.

I originally thought that the values (with the extra decimals) were being
carried over directly from Excel, but I have verified that this is not the
case. The value is actually 622.9 period!

Now, allow me to clarify what I mean by 'import':
1. Select & Copy rows from my Excel spreadsheet.
2. Paste them directly into the Datasheet View of a Select Query that I
created, specifically to accept this data.

Note: The trickiest part (for me) was that the data appears normal in the
Select Query after being imported. I didn't even realize that the extra
decimal places were there until I tried to perform a Sum calculation on the
data.

Also, it doesn't seem to make any difference if I try to set up the Field
Properties of my Select Query to only accept a Fixed format with 1 Decimal
Place, prior to importing?

How can I fix this problem and make sure that 622.9 is actually stored as
622.9?

 

[Ken Snell \(MVP\)]

ACCESS is storing your noninteger value as a precision floating point
number, which means that it's not exactly the number you see in EXCEL. This
is how computers store noninteger values, and is not an artifact of the
import process.

If you want to get to a more precise decimal number, there are two
approaches that you might take:

1) Import the data into an existing table, and set the data type of the
field that will receive the 622.9 value to Currency. Currency is a special
noninteger data type that stores 4 decimal places accurately; therefore, it
will hold the value 622.9 and not the 622.900024414063 value that you're
currently getting.

2) Import the data to a temporary or new table, and then use an append query
to copy the data from the temp/new table into the permanent table. Again,
have the permanent table's field for this value be formatted as Currency.

ACCESS does have a Decimal datatype, which allows you to specify the exact
number of decimal places to be stored, but it tends to be a bit flakey at
times with other features of ACCESS, and is not always compatible with other
databases.

 

[Guest]

Thanks Ken,

Your suggestion of switching from number to currency data type, worked
perfectly!

Sincerely,
Access''03_NewUser.