| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 78660 | 2007-04-24 01:28:00 | Access Quey Export to Excel | Alatel (12177) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 543698 | 2007-04-24 01:28:00 | I have a database (Access 2003) which includes number fields in the table (single). When I run a query then export the result to Excel the number shows additional decinal points if the number in the field is x.1, x.2, x.3, x.4 but is correct with x.5. 4054.1 shows up as 4054.10009765625 4054.2 shows up as 4054.19995117188 4118.1 shows up as 4118.10009765625 4118.2 shows up as 4118.2001953125 4155.1 shows up as 4155.10009765625 4155.2 shows up as 4155.2001953125 4155.3 shows up as 4155.2998046875 4155.4 shows up as 4155.39990234375 I have created a simple database with 2 fields and this does the same. Any suggestions why this is happening. Cheers: |
Alatel (12177) | ||
| 543699 | 2007-04-24 04:27:00 | Change the cells format in Excel, from "General" to "Number", and select to display only 1 decimal point. | What Do I Know? (10671) | ||
| 543700 | 2007-04-24 04:48:00 | I know how to overcome the problem in Excel but I was wondering why Access is storing the number with the additional decimal points when they were NOT entered Cheers |
Alatel (12177) | ||
| 543701 | 2007-04-24 07:50:00 | Usual MS software rounding things up so that bills billions keep growing... nah... I thinks its just standard number staorage handling, since its all binary underneath and we all want to see it in decimals something is bound to get out of wack. |
zcc (50) | ||
| 543702 | 2007-04-24 09:44:00 | It seems that if you use a "double" float type in Access, it will export cleanly to Excel. Problem arises if you convert an existing "single" type to "double" in Access, you introduce 'rounding' errors in the conversion - you would have to overtype the numbers to correct this. | wuppo (41) | ||
| 543703 | 2007-04-24 11:29:00 | It seems that if you use a "double" float type in Access, it will export cleanly to Excel. Problem arises if you convert an existing "single" type to "double" in Access, you introduce 'rounding' errors in the conversion - you would have to overtype the numbers to correct this. Try running an update query to round the decimals to want you want...? :thumbs: |
zcc (50) | ||
| 543704 | 2007-04-25 07:58:00 | Thanks Wuppo I have changed the "Number" field size to double as you advised and corrected the few entries in the Database and this seems to have solved the problem. Do you have any idea why using double works and not single as the numbers involved are only between 4000 and 5000? Thanks again Alan |
Alatel (12177) | ||
| 543705 | 2007-04-25 08:55:00 | OOPS Spoke too soon !! :help: Tried the same thing on another number field and this "fix" doesn't work, when you view the table data looks correct, the problem only arises when the table is exported to excel. I want to retain the "auto" decimal setting as the majority of numbers entered are whole numbers. I have made another discovery, numbers ending with .25, .5 & .75 have no problem (if this helps) Alan |
Alatel (12177) | ||
| 543706 | 2007-04-25 09:11:00 | Hi Alan What method are you using to export the numbers to Excel? It sounds like a floating point error but those values seem way to high for that. Andrew |
andrew93 (249) | ||
| 543707 | 2007-04-26 07:29:00 | Hi Andrew From database table view select export from the "File" drop-down list, choose a name for the file then select Microsoft Excel 1997-2003 from the "Save as type:" drop-down box. Cheers Alan |
Alatel (12177) | ||
| 1 2 | |||||