Log In
Register
Home
August 24, 2018
Offline
Hi, i'm having issues with summing and calculating data in access as it seems to produce slightly different answers to those i'm expecting so i decided to take the basic data into excel and do the sums / calculations there. so far, so good, but when pasted into access the numbers change, only by a few pence but annoying. i can't see what i'm doing wrong and would be grateful for any (simple) advice please.
example: £690,646.85 in excel becomes £690,646.90 in access (both are set to number / standard / 2 decimal places
Thanks for reading,
Deirdre
VIP
Trusted Members
December 7, 2016
Offline
Hello,
In Access design view, change the number field to:
Data type = Number
Field size = Single
Format = General number
Access will then store and display the decimals as entered.
Br,
Anders
August 24, 2018
Offline
Hi Anders, thanks for your reply, but unfortunately it doesn't seem to work (not for me anyway).
Deirdre
VIP
Trusted Members
December 7, 2016
Offline
Hello,
This post is a bit off topic for this forum, but I give it a try anyway to see if we can find out where the issue lies.
Access handles decimals differently based on which setting you use. If you change the setting and you have existing data in that column, then most likely all those numbers need to be re-typed again.
In attached picture below I have tried to show how it works, the number I have used in this example is 1006,867 (comma is the decimal separator in Sweden) and as is shown Access handles this differently based on the settings. For the Single and Double field sizes the stored value is the entered number with three decimals, but it only displays that number with the General format. With the Long Integer field size it changes the number stored in the field to nearest integer, no decimals (a big surprise otherwise).
So, in my tests I have not been able to recreate your issue. How does it work if you type in the number value instead of pasting it? Another thing to check is if the value copied from Excel is correct, with two decimals and so forth, what do you get if you paste it to a page in Notepad for instance.
Br,
Anders
Trusted Members
October 18, 2018
Offline
Deidre
Taking another approach, please attach a sample excel sheet and a sample access workbook showing your issue. Tell us exactly how you take the data from excel and put it into access. Let us examine this and see if we can come up with an answer. I have never seen this type of issue.
BTW: Have you considered linking your excel sheet to access so that you don't have to re-enter the data?
August 24, 2018
Offline
Hi Alan and Anders, and many thanks for your replies; I gave up trying to get the number right and as it appeared to be the only incorrect number, i have edited the data in Access (time constraints).
it was really bizarre as the append queries in Access gave the wrong total, but separate MS queries run in Excel produced the correct total, yet copied and appended back into Access changed. Sadly I don't have time to explore further now, but will be annoyed if the same issue arises when updating (annual data) next year.
Thanks again, Deirdre
1 Guest(s)
