Help Please - Decimal Points Rnding Off

[nancybrown]

I'm importing an excel spreadsheet that has a field formatted for 5 decimal places, e.g., 1.31758.

When importing into Access, it is rounding down, e.g., 1. What do I need to do to stop this? I need field to display exactly as is without round . . . and 5 decimal places.

Regards,

 

[Ron Coderre]

It sounds like you have the receiving table field set to a number type that only includes whole numbers (like Integer). Try changing it to something that accepts decimal places.

Does that help?

 

[nancybrown]

Hi Ron,

The receiving table is set to number type with long integer as the field size. I attempted to change the field size to "decimal," as well as changing the field type to text . . . am receiving an error message "not enough disk space or memory." What kind of trouble am I in now?

 

[Ron Coderre]

There may be something to be learned by trying to import the data into a new table (created upon import).
Does that execute without incident?

 

[alansidman]

I find it easier to do what Ron suggested. Additionally, I find that you have more control over the importation if you convert your excel file to .csv file. You can then pick and choose how you want each field to be formatted upon import rather than let Access guess. If this is going to be a repeat excercise, then do it the first time manually and save the specification so you can do the same over and over again.

Alan

 

[Joe4]

The receiving table is set to number type with long integer as the field size

Integers do not allow decimals! You will need to choose another Field Size option, such as Decimal, Single, or Double.

Try Double, as that is one of that should allow the most possibilities.

. am receiving an error message "not enough disk space or memory."

How much data are you trying to import?
What is the size of your database?

 

[nancybrown]

Gentlemen, thanks for your patience. I think there is a misunderstanding of my issue.
My data has already been imported successful. The problem I'm faced with is that the decimal field is displaying as either rounded up or rounded down . . . instead of 1.38592 it is rounding down to 1.00000 or if 1.59983 is rounding up to 2.

I understand now that the field size should be "double," however, when I attempt to make that change and save is when I am getting the "not enough memory" and will not let me make the change.

 

[Joe4]

If the data has already been imported, it is too late to make the change on the existing data, it was rounded off upon import and is stored in the table rounded off, so you lost all decimal precision on any existing data.

In order to keep the decimals, you will need to re-import your data. If you are importing into a new table, you will need to specify one of those other Field Size Options as you create the table upon import. If importing into an existing table, you will need to change the Field Size option first before importing.

If you are running into errors on trying to change that Field Size option, try Compacting & Repairing the database first. If that still doesn't work, you may need to create a new table instead of trying to alter an existing one. If that works, you can always move data from the old table to the new table via an Append Query (though your old table data probably has the rounded off values you don't want, so I don't think you want that anyway).

 

[nancybrown]

Joe/Ron/Alan . . . Many thanks for your help.