Calculation Problems ... Again...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi again,

I seem to have NO luck with calculations. The expressions I've used work
like their supposed to but it's the END RESULT that's wrong. Here's an
example:

In the subform I have:
Qty Received Price Per UoM Price Each Qty X Price Each
Adjustment
5 $10.98 $2.20
$10.98 $0.00

As you can see $2.20 X 5 is NOT $10.98 - it is $11.00.

What am doing wrong or actually NOT doing the right way? Is there something
that I have to change in one (or all) of the fields properties?

Thanks for any help.

Regards,

Kathleen
 
Let's try this another way:

I seem to have NO luck with calculations. The expressions I've used work
like their supposed to but it's the END RESULT that's wrong. Here's an
example:

In the subform I have:

Qty Received - 5
Price Per UoM - $10.98
Price Each - $2.20
Qty X Price Each - $10.98
Adjustment - $0.00

As you can see $2.20 X 5 is NOT $10.98 - it is $11.00.
 
Are you using Currency as your data type?

Brian

BardsSweetie said:
Let's try this another way:

I seem to have NO luck with calculations. The expressions I've used work
like their supposed to but it's the END RESULT that's wrong. Here's an
example:

In the subform I have:

Qty Received - 5
Price Per UoM - $10.98
Price Each - $2.20
Qty X Price Each - $10.98
Adjustment - $0.00

As you can see $2.20 X 5 is NOT $10.98 - it is $11.00.
Click to expand...
 
Kathleen,

Floating point numbers aren't as accurate as we would like. When the get
converted to binary and back again, round off errors occur. First, is $2.20
the actual value being multiplied or is this number itself the result of a
calculation? If the latter, it may show as $2.20 when formatted, but it may
actually be a slightly different value. The reason I ask is that I wouldn't
expect the floating point errors to creep in on something as simple as 5 *
2.20 (in testing, I get 11). Next, there are data types that handle floating
point numbers that don't have this problem. One of them is the Currency data
type. If you don't need more than 4 decimal places of accuracy, this will
work. However, be aware, the round-off when formatted (from 4 decimal
places to 2) can still cause a problem between what you see and what's
actually the value of the number. You can use the Currency data type for
numbers other than currency if you want the increased accuracy, just
remember the 4 decimal place limitation and just set the Format property of
the field to other than Currency. The other data type that will give you the
increased accuracy and isn't limited to 4 decimal places is the Decimal data
type. VBA doesn't have a Decimal data type (your tables do, though), so you
have to use Variants to hold the numbers and assign the numbers to the
Variants using the CDec() function.

If the actual value is different (i.e. it's being rounded off), you need to
decide whether you want the increased accuracy or if you want the rounded
value that you see be the value that is stored and/or used. If the first,
you'll either need to display more decimal places so that you can see what
is happening or just accept the fact that you are looking at rounded values.
If the second option, you'll need to round off the values yourself to change
them to the rounded value, not just display them that way.

If you want to round the values, there is a built-in Round() function.
However, may folks don't like it because it uses what is know as "banker's"
or "scientific" rounding. It will round .5 to the nearest even number, not
round it up every time. If you want it to round .5 up every time, you'll
need to create your own rounding function and call it.

Example round to 2 decimals:
Public Function MyRound(curInput As Currency) As Currency
MyRound = Int(curInput * 100 + 0.5) / 100
End Function
 
Back
Top