Access Rounding Dollar Amounts Up When Not Wanted

[dev513]

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]