 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / March 2007Formatting in Access
I am bringing in data from 2 systems, then comparing them for outages. I bring all data in as Double, standard, 2 decimals. All data looks good. But then when I subtract the 2 fields on SOME, not all I get 363797880708171E-12, yet when you look at the 2 fields that are identical and NOT out of balance. Even those 'true' fields that are out of balance, sometimes I just get the dollar amount and some times I get that .458759862335412 stuff. I have tried formatting the query, I have formatted the report and nothing seems to work. Frustrated KY Lady! Thanks Vivian Floating point fields (single and double) are subject to "floating point errors" because they don't translate exactly to decimal values in all cases. Take a look at the article "When Access Math doesn't add up" at www.fmsinc.com for a detailed explanation. To avoid the issue entirely, I always use currency fields rather than single or double unless four decimal digits is not enough precision. If I need more precision, I scale myself or suffer the floating point errors. Usually they are so small that they don't matter. They only get in the way when you are comparing two fields for equality. Sometimes you can consider two fields = if the difference is a tiny amount. The currency data type is actually a scaled integer so it doesn't have the conversion issues that floating point numbers which are binary do. >I am bringing in data from 2 systems, then comparing them for outages. > I bring all data in as Double, standard, 2 decimals. All data looks good. [quoted text clipped - 10 lines] > Thanks > Vivian Pat Thanks, but 1 field is hours and the other earnings, and I am having the same issue with both fields. I think it has something to do with what I am doing. One table has a 1 total per employee. The other system has all kinds of hrs/erngs per period by employee. So instead of trying to do everything within a query I took a query, summed up my totals and did a append table. so now I have 2 tables with 1 employee and totals. It now looks as if they simple subtract is working. My ultimage goal after the query compares all fields I just want a report of those that are out. So my query that does subtract them now looks good. But when I try to say just give me anyone with hours or earnings <>0, I still get to many records that are balanced. when I try to append to another table, it brings in the -3.045789569 even though when I view it prior to running the make table and it shows 0.00. Sorry, but I have been working on this all day and the simply formatting issue is drving me to want to DRINK! Anything else you can offer is very much appreciated. > Floating point fields (single and double) are subject to "floating point > errors" because they don't translate exactly to decimal values in all cases. [quoted text clipped - 23 lines] > > Thanks > > Vivian Defining a field as currency doesn't mean that it needs to be displayed as such. The point is that the currency data type is "fixed point" rather than "floating point" and therefore isn't subject to floating point errors. Read my answer again and find the article I referenced. > Pat > [quoted text clipped - 57 lines] >> > Thanks >> > Vivian On Mar 21, 5:34 pm, "Pat Hartman \(MVP\)" <please no e...@aol.com> wrote: > Floating point fields (single and double) are subject to "floating point > errors" because they don't translate exactly to decimal values in all cases. [quoted text clipped - 3 lines] > digits is not enough precision. If I need more precision, I scale myself or > suffer the floating point errors. There is an alternative, being the DECIMAL type: fixed point decimal, custom scale and precision, no implication of money data (but is perfectly suited for the purpose), truncates rather than rounds by nature, etc. It's also the native decimal type in Access/Jet SQL code e.g. SELECT TYPENAME(0.33) returns 'Decimal'. Jamie. -- On Mar 21, 5:34 pm, "Pat Hartman \(MVP\)" <please no e...@aol.com> wrote: > Floating point fields (single and double) are subject to "floating point > errors" because they don't translate exactly to decimal values in all cases. > Take a look at the article "When Access Math doesn't add up" at www.fmsinc.com for a detailed explanation. FWIW this article only applies to VBA code. Decimal literals in Access/ Jet SQL code are of the DECIMAL type in nature. To take the article's first example: SELECT 100.8 - 100.7 correctly returns 0.1. (as type DECIMAL). Jamie. -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.