Access Rounding Dollar Amounts Up When Not Wanted

[Guest]

I am using Access 2003. I imported individual spreadsheets from Excel into
Access, at first everything was fine. At some point Access rounded all the
dollar amounts up to the nearest dollar (Example: 6.95 became 7.95).
First Question: Why did this happen?
Second Question: Can it be fixed without manually changing all prices back.
Third Question: How do I keep it from happening again.

 

[John Vinson]

I am using Access 2003. I imported individual spreadsheets from Excel into
Access, at first everything was fine. At some point Access rounded all the
dollar amounts up to the nearest dollar (Example: 6.95 became 7.95).

$7.00 I presume??

First Question: Why did this happen?

Probably because the datatype of the field somehow got set to
Number... Long Integer, the default Number datatype.

When you're importing from Excel, Access must guess at the appropriate
datatype, since spreadsheet cells don't have "strong typing". It
sometimes guesses wrong.

Second Question: Can it be fixed without manually changing all prices back.

Nope. Reimporting is your only hope.

Third Question: How do I keep it from happening again.

I'd suggest creating the table in Access, empty, with the proper
choice of datatypes. For money data, use the Currency datatype (it's
not one of the Number datatypes but a separate type along with
date/time, yes/no, text and so on). Link to the spreadsheet and run an
Append query to move the data into your defined table, rather than
importing the spreadsheet.


John W. Vinson[MVP]

 

[onedaywhen]

Probably because the datatype of the field somehow got set to
Number... Long Integer, the default Number datatype.

The following KB article on MS products' rounding algorithms may
provide some answers:

http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

When you're importing from Excel, Access must guess at the appropriate
datatype, since spreadsheet cells don't have "strong typing". It
sometimes guesses wrong.

MS Access doesn't do the guessing. Rather, it is done before the data
leaves Excel (it's done by Jet, but by Jet on the Excel side, rather
than Jet on the MS Access side i.e. it's simplest to think of Excel
doing it's own guessing).

I'd suggest creating the table in Access, empty, with the proper
choice of datatypes.
Link to the spreadsheet and run an
Append query to move the data into your defined table, rather than
importing the spreadsheet.

Creating a table in the database will not influence Excel's choice of
data types. In fact, it may introduce a further rounding algorithms
when the data is placed in the table, leading to further confusion

Jamie.

--