If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Rounding Dollar Amounts Up When Not Wanted
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. |
#2
|
|||
|
|||
On Thu, 17 Mar 2005 08:49:15 -0800, dev513
wrote: 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] |
#3
|
|||
|
|||
John Vinson wrote: 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 Why did this happen? 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...b;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. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What is the difference between 2002 and 2003? | Red Sonya | General Discussion | 2 | March 1st, 2005 05:10 AM |
starting access 97 | Edward Letendre | General Discussion | 2 | January 26th, 2005 02:15 AM |
How do merge letter with different dollar amounts in word | BREADBASKET | Mailmerge | 2 | November 11th, 2004 04:28 PM |
Upload Image | Jason MacKenzie | General Discussion | 1 | September 1st, 2004 04:38 AM |
rounding dollar amounts | Dave | Worksheet Functions | 2 | November 1st, 2003 03:32 PM |