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
|
|||
|
|||
Anyone had different results between Access calculations and Excel
I've been programming in Access since 1995, but this is the first time I've
run across what appears to be some discrepancies between calculations of data in Access and the same calculations being done in Excel with imported raw data from Acess. I've looked over my formatting, formulas, and data sources, and can find no answer except that there must be some problem int this case with Access calculations of floating numbers. Has anyone else had an issue where Access calculations of data differed from the same calculations in Excel? Any suggestions? |
#2
|
|||
|
|||
Anyone had different results between Access calculations and Excel
You have to be careful, as the 1st thing you lean in a computing course is
that rounding occurs. eg: Public Sub TestAdd() Dim MyNumber As Single Dim i As Integer For i = 1 To 10 MyNumber = MyNumber + 1.01 Debug.Print MyNumber Next i End Sub Here is the actual outpput of the above: 1.01 2.02 3.03 4.04 5.05 6.06 7.070001 8.080001 9.090001 10.1 You can see that after just 7 addtions..already rounding is occuring ! and if we add the follwing line of code to the end of the above: if MyNumber = 10.1 = True then msgbox "the number is 10.1" else msgbox "the number is somthing else" endif The above will actuall produce: the number is something else In other words, even when it SHOWS 10.1, and we "test" for that value, it fails. If you doing additions of data, hopefully your numbers do not need more then 4 decimal places. If that is the case, then use a currency field, NOT floating number type field. -- floating numbers in a computer are only a approximate representation of the actual value (we can't really represent fractional values). A currency field is actually scaled integer (that means it is a integer with some decimal settings not an actual computer floating point number). If you can't use currency, then consider using "decimal field". It allows up to 28 digits, and a scale to that... Currency is better if your numbers never need more then 4 decimal point because we have a native built in currency data type. if you use decimal..then you need to use a variant type. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#3
|
|||
|
|||
Anyone had different results between Access calculations and E
Thanks, Albert. I appreciate what you're saying, and while I didn't know
quite all the detail, I do have a lot of experience with Access programming professionally and am aware of the rounding issues. My understading is that in reports (and text fields) that the format property mostly affects how something is displayed. Unfortunately, my calculated field does need to be quite precise. In the Access documentation I'm seeing a lot of use of the expression "follow the settings specified in the regional settings of Windows for negative amounts" along with what the particular formatting choice should display in the way of comma, decimal point, and currency sign. This isn't telling me anything about what data type is "under the hood" doing the work for the calculating formula. I guess I really was interested to know if there are any indications of a documented flaw in Access in this case, rather than user error involving rounding. I'm afraid I may just have to reevaluate the underlying sources of my calculations and try to have closer control over the calculating field itself, perhaps with use of a custom function that could enforce the data type I wish. Thanks for your help considering this. I welcome any follow-up comments you might have. Best Regards, DoogieB "Albert D. Kallal" wrote: You have to be careful, as the 1st thing you lean in a computing course is that rounding occurs. eg: Public Sub TestAdd() Dim MyNumber As Single Dim i As Integer For i = 1 To 10 MyNumber = MyNumber + 1.01 Debug.Print MyNumber Next i End Sub Here is the actual outpput of the above: 1.01 2.02 3.03 4.04 5.05 6.06 7.070001 8.080001 9.090001 10.1 You can see that after just 7 addtions..already rounding is occuring ! and if we add the follwing line of code to the end of the above: if MyNumber = 10.1 = True then msgbox "the number is 10.1" else msgbox "the number is somthing else" endif The above will actuall produce: the number is something else In other words, even when it SHOWS 10.1, and we "test" for that value, it fails. If you doing additions of data, hopefully your numbers do not need more then 4 decimal places. If that is the case, then use a currency field, NOT floating number type field. -- floating numbers in a computer are only a approximate representation of the actual value (we can't really represent fractional values). A currency field is actually scaled integer (that means it is a integer with some decimal settings not an actual computer floating point number). If you can't use currency, then consider using "decimal field". It allows up to 28 digits, and a scale to that... Currency is better if your numbers never need more then 4 decimal point because we have a native built in currency data type. if you use decimal..then you need to use a variant type. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#4
|
|||
|
|||
Anyone had different results between Access calculations and E
On Mon, 16 Jul 2007 09:52:07 -0700, DoogieB
wrote: Thanks, Albert. I appreciate what you're saying, and while I didn't know quite all the detail, I do have a lot of experience with Access programming professionally and am aware of the rounding issues. My understading is that in reports (and text fields) that the format property mostly affects how something is displayed. Unfortunately, my calculated field does need to be quite precise. In the Access documentation I'm seeing a lot of use of the expression "follow the settings specified in the regional settings of Windows for negative amounts" along with what the particular formatting choice should display in the way of comma, decimal point, and currency sign. This isn't telling me anything about what data type is "under the hood" doing the work for the calculating formula. I guess I really was interested to know if there are any indications of a documented flaw in Access in this case, rather than user error involving rounding. I'm afraid I may just have to reevaluate the underlying sources of my calculations and try to have closer control over the calculating field itself, perhaps with use of a custom function that could enforce the data type I wish. Thanks for your help considering this. I welcome any follow-up comments you might have. Best Regards, DoogieB "Albert D. Kallal" wrote: You have to be careful, as the 1st thing you lean in a computing course is that rounding occurs. eg: Public Sub TestAdd() Dim MyNumber As Single Dim i As Integer For i = 1 To 10 MyNumber = MyNumber + 1.01 Debug.Print MyNumber Next i End Sub Here is the actual outpput of the above: 1.01 2.02 3.03 4.04 5.05 6.06 7.070001 8.080001 9.090001 10.1 You can see that after just 7 addtions..already rounding is occuring ! and if we add the follwing line of code to the end of the above: if MyNumber = 10.1 = True then msgbox "the number is 10.1" else msgbox "the number is somthing else" endif The above will actuall produce: the number is something else In other words, even when it SHOWS 10.1, and we "test" for that value, it fails. If you doing additions of data, hopefully your numbers do not need more then 4 decimal places. If that is the case, then use a currency field, NOT floating number type field. -- floating numbers in a computer are only a approximate representation of the actual value (we can't really represent fractional values). A currency field is actually scaled integer (that means it is a integer with some decimal settings not an actual computer floating point number). If you can't use currency, then consider using "decimal field". It allows up to 28 digits, and a scale to that... Currency is better if your numbers never need more then 4 decimal point because we have a native built in currency data type. if you use decimal..then you need to use a variant type. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada Open Excel Enter 1 in A1 Enter3 in A2 Enter =a1/A2 in A3 Enter = a3 * 10 - A$2 in A4 Copy A4 to A5 : A25 look at the results. This is because in computer calculations, no mater how many decimal places you format the cells A1 : A25, The last place is always *uncertain* . And this *uncertainty* creeps forward with each successive calculation. A hand held electronic *Calculator* does math a little differently and the same *trick* will run the value to zero then -3, -33, -333 etc. Chuck -- |
#5
|
|||
|
|||
Anyone had different results between Access calculations and E
1) Access rounds differently than Excel at every step of the calculation.
2) Access round differently than Excel before display. Access internally uses Single or Double floating point numbers. Excel internally uses 8087 IEEE floating point numbers. Excel rounds to Double floating point only at the end of the calculation. Then Excel rounds for display. I don't think this is a flaw, but it is different. VB uses standard floating point numbers. Excel has a dedicated calculation engine. Excel is the standard: Compatibility is not an issue. But it is tightly bound to the hardware platform. Still, if you do it properly, Access/VBA with Double accuracy is almost always close enough to Excel. It would be a very unusual problem that didn't give the same answer as either (your Excel spreadsheet) or (the other party reference) when done carefully. Don't use Single. (david) "DoogieB" wrote in message ... Thanks, Albert. I appreciate what you're saying, and while I didn't know quite all the detail, I do have a lot of experience with Access programming professionally and am aware of the rounding issues. My understading is that in reports (and text fields) that the format property mostly affects how something is displayed. Unfortunately, my calculated field does need to be quite precise. In the Access documentation I'm seeing a lot of use of the expression "follow the settings specified in the regional settings of Windows for negative amounts" along with what the particular formatting choice should display in the way of comma, decimal point, and currency sign. This isn't telling me anything about what data type is "under the hood" doing the work for the calculating formula. I guess I really was interested to know if there are any indications of a documented flaw in Access in this case, rather than user error involving rounding. I'm afraid I may just have to reevaluate the underlying sources of my calculations and try to have closer control over the calculating field itself, perhaps with use of a custom function that could enforce the data type I wish. Thanks for your help considering this. I welcome any follow-up comments you might have. Best Regards, DoogieB "Albert D. Kallal" wrote: You have to be careful, as the 1st thing you lean in a computing course is that rounding occurs. eg: Public Sub TestAdd() Dim MyNumber As Single Dim i As Integer For i = 1 To 10 MyNumber = MyNumber + 1.01 Debug.Print MyNumber Next i End Sub Here is the actual outpput of the above: 1.01 2.02 3.03 4.04 5.05 6.06 7.070001 8.080001 9.090001 10.1 You can see that after just 7 addtions..already rounding is occuring ! and if we add the follwing line of code to the end of the above: if MyNumber = 10.1 = True then msgbox "the number is 10.1" else msgbox "the number is somthing else" endif The above will actuall produce: the number is something else In other words, even when it SHOWS 10.1, and we "test" for that value, it fails. If you doing additions of data, hopefully your numbers do not need more then 4 decimal places. If that is the case, then use a currency field, NOT floating number type field. -- floating numbers in a computer are only a approximate representation of the actual value (we can't really represent fractional values). A currency field is actually scaled integer (that means it is a integer with some decimal settings not an actual computer floating point number). If you can't use currency, then consider using "decimal field". It allows up to 28 digits, and a scale to that... Currency is better if your numbers never need more then 4 decimal point because we have a native built in currency data type. if you use decimal..then you need to use a variant type. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#6
|
|||
|
|||
Anyone had different results between Access calculations and E
On Jul 17, 1:31 am, david@epsomdotcomdotau wrote:
Access internally uses Single or Double floating point numbers. Access's SQL engine does not e.g. SELECT TYPENAME(0.5) returns 'Decimal'. Jamie. -- |
#7
|
|||
|
|||
Anyone had different results between Access calculations and E
"Jamie Collins" wrote
Access internally uses Single or Double floating point numbers. Access's SQL engine does not e.g. SELECT TYPENAME(0.5) returns 'Decimal'. And, as your comment does not appear to deal with the subject under discussion: internal calculations, your point is exactly _what_? |
#8
|
|||
|
|||
Anyone had different results between Access calculations and E
The example demonstrates that a manifest constant may be
treated like a Decimal by Jet, which, on the face of it, falsifies my assertion that Access internally uses Single or Double floating point numbers. So questions are (1) What calculation engine is used by Jet? (2) What is the internal representation for Decimal? and (3) Is the treatment of manifest constants relevant? I think that SQL will probably use a VBA like calculation engine: that is, Jet is probably compiled by MSC++, the same as VBA. Excel is not. There is no reason to expect that the hand-tuned assembly language calculation engine has been discarded. As everyone knows, Decimal is a kludge that has been tacked on to VBA and Jet. It is extremely unlikely that VBA/Jet inherited any of the Excel calculation engine: it is also unlikely that any of the existing Single/Double calculation engine was touched. Unless anyone demonstrates otherwise, my guess is that the treatment of manifest constants is an anomaly. (david) "Larry Linson" wrote in message ... "Jamie Collins" wrote Access internally uses Single or Double floating point numbers. Access's SQL engine does not e.g. SELECT TYPENAME(0.5) returns 'Decimal'. And, as your comment does not appear to deal with the subject under discussion: internal calculations, your point is exactly _what_? |
Thread Tools | |
Display Modes | |
|
|