Formatting in Access

V

Vivian Baker

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
 
P

Pat Hartman \(MVP\)

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.
 
V

Vivian Baker

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.
 
P

Pat Hartman \(MVP\)

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.
 
Top